Calculating Downtime

M

mrpoo

New Member
Joined
Apr 12, 2018
Messages
17
Hi,

I have a spreadsheet connected to a SQL database that holds data from two pieces of test equipment, I want to create an equipment efficiency tracker that calculates the total idle time.
The data comes from two testers (Tuner 1 and Tester 2) and date (B), time stamp(c) and total test time(D) in seconds is also present:,

Total Idle Time - How can I get the total idle time to display in column I (bottom image) for Tuner 1 only, but broken down into the various hr sections that are in column A (bottom image)?
I guess something like C+D - next Tuner 1 time stamp?

Help, well over my head with this one!

A B C D
Tuner 124/06/202112:46:13141.6
Tuner 224/06/202112:47:54152.5
Tuner 124/06/202112:53:12140.2
Tuner 224/06/202112:53:43152.5
Tuner 124/06/202113:35:26141.5
Tuner 124/06/202113:35:47151.4

A C E G I K
1624616149801.png
 

Attachments

  • 1624615801711.png
    1624615801711.png
    25.1 KB · Views: 3

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
I dont see any indicator f idle time.

you can use sum, sumif, sumifs, sumproduct to sum up time. but it all requires a range to sum up and criteria.
in those screenshots I do see only timestamps but I don't see anything indicating downtime.
 
Upvote 0
Radoslaw Poprawski said:
I dont see any indicator f idle time.

you can use sum, sumif, sumifs, sumproduct to sum up time. but it all requires a range to sum up and criteria.
in those screenshots I do see only timestamps but I don't see anything indicating downtime.
Click to expand...
Wouldn't it be the finish time of one test cycle (start time + cycle time) then the time between that and the start of the next cycle?
 
Upvote 0
example.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
4
5
625/06/2021
7Friday0780002850028500100.0%0.0%100.0%0.0%
8
907:00 - 08:00010600600100.0%0.0%100.0%0.0%
1007:00:00###
1108:00 - 09:00010036003600100.0%0.0%100.0%0.0%
1208:00:00###
1309:00 - 10:00010036003600100.0%0.0%100.0%0.0%
1409:00:00###
1510:00 - 11:0007024002400100.0%0.0%100.0%0.0%
1610:00:00###
1711:00 - 12:00010036003600100.0%0.0%100.0%0.0%
1811:00:00###
1912:00 - 13:00010036003600100.0%0.0%100.0%0.0%
2012:00:00###
2113:00 - 14:0005018001800100.0%0.0%100.0%0.0%
2213:00:00###
2314:00 - 15:00010036003600100.0%0.0%100.0%0.0%
2414:00:00###
2515:00 - 16:0008027002700100.0%0.0%100.0%0.0%
2615:00:00###
2716:00 - 17:0007030003000100.0%0.0%100.0%0.0%
2816:00:00##
Data
Cell Formulas
RangeFormula
B6B6=TODAY()
B7B7=WEEKDAY(B6)
E7,I7,G7E7=SUM(E9+E11+E13+E15+E17+E19+E21+E23+E25+E27)
K7,U7,S7,Q7K7=K9+K11+K13+K15+K17+K19+K21+K23+K25+K27
W7,AC7,AA7,Y7W7=SUM(W9+W11+W13+W15+W17+W19+W21+W23+W25+W27)/10
E9,E27,E25,E23,E21,E19,E17,E15,E13,E11E9=COUNTIFS(BUILD_DATA[TEST_DATE],Data!$AU$1,BUILD_DATA[TEST_RESULT],"Pass",BUILD_DATA[TEST_TIME], ">="&B10,BUILD_DATA[TEST_TIME],"<"&C10,BUILD_DATA[WORKS_ORDER_NUMBER],"Tuner 1")
G9G9=IF(B7="Friday",Lookups!H3,Lookups!F3)
I9,I27,I25,I23,I21,I19,I17,I15,I13,I11I9=COUNTIFS(BUILD_DATA[TEST_DATE],Data!$AU$1,BUILD_DATA[TEST_RESULT],"Fail",BUILD_DATA[TEST_TIME], ">="&B10,BUILD_DATA[TEST_TIME],"<"&C10,BUILD_DATA[WORKS_ORDER_NUMBER],"Tuner 1")
S9S9=IF(B7="Friday", "600", "600")-K9-Q9
U9U9=IF(B7="Friday", "600", "600")
W9,W27,W25,W23,W21,W19,W17,W15,W13,W11W9=S9/U9
Y9,Y27,Y25,Y23,Y21,Y19,Y17,Y15,Y13,Y11Y9=SUM(330*E9)/S9
AA9AA9=IF(E9=0,"100.0%",(I9-(I9))/E9)
AC9,AC27,AC25,AC23,AC21,AC19,AC17,AC15,AC13,AC11AC9=W9*Y9*AA9
G11G11=IF(B7="Friday",Lookups!H4,Lookups!F4)
S11S11=IF(B7="Friday", "3600", "3600")-K11-Q11
U11U11=IF(B7="Friday", "3600", "3600")
AA11AA11=IF(E11=0,"100.0%",(E13-(I11))/E13)
G13G13=IF(B7="Friday",Lookups!H5,Lookups!F5)
S13S13=IF(B7="Friday", "3600", "3600")-K13-Q13
U13U13=IF(B7="Friday", "3600", "3600")
AA13,AA27,AA25,AA23,AA21,AA19,AA17,AA15AA13=IF(E13=0,"100.0%",(E13-(I13))/E13)
G15G15=IF(B7="Friday",Lookups!H6,Lookups!F6)
S15S15=IF(B7="Friday", "2400", "2400")-K15-Q15
U15U15=IF(B7="Friday", "2400", "2400")
G17G17=IF(B7="Friday",Lookups!H7,Lookups!F7)
S17S17=IF(B7="Friday", "3600", "3600")-K17-Q17
U17U17=IF(B7="Friday", "3600", "3600")
G19G19=IF(B7="Friday",Lookups!H8,Lookups!F8)
S19S19=IF(B7="Friday", "600", "3600")-K19-Q19
U19U19=IF(B7="Friday", "600", "3600")
G21G21=IF(B7="Friday",Lookups!H9,Lookups!F9)
S21S21=IF(B7="Friday", "0", "1800")-K21-Q21
U21U21=IF(B7="Friday", "0", "1800")
G23G23=IF(B7="Friday",Lookups!H10,Lookups!F10)
S23S23=IF(B7="Friday", "0", "3600")-K23-Q23
U23U23=IF(B7="Friday", "0", "3600")
G25G25=IF(B7="Friday",Lookups!H11,Lookups!F11)
S25S25=IF(B7="Friday", "0", "2700")-K25-Q25
U25U25=IF(B7="Friday", "0", "2700")
G27G27=IF(B7="Friday",Lookups!H12,Lookups!F12)
S27S27=IF(B7="Friday", "0", "3000")-K27-Q27
U27U27=IF(B7="Friday", "0", "3000")
 
Upvote 0
Radoslaw Poprawski said:
provide minisheet please.
Click to expand...

mrpoo said:
example.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
4
5
625/06/2021
7Friday0780002850028500100.0%0.0%100.0%0.0%
8
907:00 - 08:00010600600100.0%0.0%100.0%0.0%
1007:00:00###
1108:00 - 09:00010036003600100.0%0.0%100.0%0.0%
1208:00:00###
1309:00 - 10:00010036003600100.0%0.0%100.0%0.0%
1409:00:00###
1510:00 - 11:0007024002400100.0%0.0%100.0%0.0%
1610:00:00###
1711:00 - 12:00010036003600100.0%0.0%100.0%0.0%
1811:00:00###
1912:00 - 13:00010036003600100.0%0.0%100.0%0.0%
2012:00:00###
2113:00 - 14:0005018001800100.0%0.0%100.0%0.0%
2213:00:00###
2314:00 - 15:00010036003600100.0%0.0%100.0%0.0%
2414:00:00###
2515:00 - 16:0008027002700100.0%0.0%100.0%0.0%
2615:00:00###
2716:00 - 17:0007030003000100.0%0.0%100.0%0.0%
2816:00:00##
Data
Cell Formulas
RangeFormula
B6B6=TODAY()
B7B7=WEEKDAY(B6)
E7,I7,G7E7=SUM(E9+E11+E13+E15+E17+E19+E21+E23+E25+E27)
K7,U7,S7,Q7K7=K9+K11+K13+K15+K17+K19+K21+K23+K25+K27
W7,AC7,AA7,Y7W7=SUM(W9+W11+W13+W15+W17+W19+W21+W23+W25+W27)/10
E9,E27,E25,E23,E21,E19,E17,E15,E13,E11E9=COUNTIFS(BUILD_DATA[TEST_DATE],Data!$AU$1,BUILD_DATA[TEST_RESULT],"Pass",BUILD_DATA[TEST_TIME], ">="&B10,BUILD_DATA[TEST_TIME],"<"&C10,BUILD_DATA[WORKS_ORDER_NUMBER],"Tuner 1")
G9G9=IF(B7="Friday",Lookups!H3,Lookups!F3)
I9,I27,I25,I23,I21,I19,I17,I15,I13,I11I9=COUNTIFS(BUILD_DATA[TEST_DATE],Data!$AU$1,BUILD_DATA[TEST_RESULT],"Fail",BUILD_DATA[TEST_TIME], ">="&B10,BUILD_DATA[TEST_TIME],"<"&C10,BUILD_DATA[WORKS_ORDER_NUMBER],"Tuner 1")
S9S9=IF(B7="Friday", "600", "600")-K9-Q9
U9U9=IF(B7="Friday", "600", "600")
W9,W27,W25,W23,W21,W19,W17,W15,W13,W11W9=S9/U9
Y9,Y27,Y25,Y23,Y21,Y19,Y17,Y15,Y13,Y11Y9=SUM(330*E9)/S9
AA9AA9=IF(E9=0,"100.0%",(I9-(I9))/E9)
AC9,AC27,AC25,AC23,AC21,AC19,AC17,AC15,AC13,AC11AC9=W9*Y9*AA9
G11G11=IF(B7="Friday",Lookups!H4,Lookups!F4)
S11S11=IF(B7="Friday", "3600", "3600")-K11-Q11
U11U11=IF(B7="Friday", "3600", "3600")
AA11AA11=IF(E11=0,"100.0%",(E13-(I11))/E13)
G13G13=IF(B7="Friday",Lookups!H5,Lookups!F5)
S13S13=IF(B7="Friday", "3600", "3600")-K13-Q13
U13U13=IF(B7="Friday", "3600", "3600")
AA13,AA27,AA25,AA23,AA21,AA19,AA17,AA15AA13=IF(E13=0,"100.0%",(E13-(I13))/E13)
G15G15=IF(B7="Friday",Lookups!H6,Lookups!F6)
S15S15=IF(B7="Friday", "2400", "2400")-K15-Q15
U15U15=IF(B7="Friday", "2400", "2400")
G17G17=IF(B7="Friday",Lookups!H7,Lookups!F7)
S17S17=IF(B7="Friday", "3600", "3600")-K17-Q17
U17U17=IF(B7="Friday", "3600", "3600")
G19G19=IF(B7="Friday",Lookups!H8,Lookups!F8)
S19S19=IF(B7="Friday", "600", "3600")-K19-Q19
U19U19=IF(B7="Friday", "600", "3600")
G21G21=IF(B7="Friday",Lookups!H9,Lookups!F9)
S21S21=IF(B7="Friday", "0", "1800")-K21-Q21
U21U21=IF(B7="Friday", "0", "1800")
G23G23=IF(B7="Friday",Lookups!H10,Lookups!F10)
S23S23=IF(B7="Friday", "0", "3600")-K23-Q23
U23U23=IF(B7="Friday", "0", "3600")
G25G25=IF(B7="Friday",Lookups!H11,Lookups!F11)
S25S25=IF(B7="Friday", "0", "2700")-K25-Q25
U25U25=IF(B7="Friday", "0", "2700")
G27G27=IF(B7="Friday",Lookups!H12,Lookups!F12)
S27S27=IF(B7="Friday", "0", "3000")-K27-Q27
U27U27=IF(B7="Friday", "0", "3000")
Click to expand...
On the Data sheet the info is in the following:

Tuner ID - C
Test Date - I
Test Time - J
Cycle Time - FD
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
300
Peter_SSs
Peter_SSs
H
  • Solved
Search for topics based on their groups.
Replies
7
Views
132
Peter_SSs
Peter_SSs
muhammad susanto
  • Solved
Concatenate Formula Text & DateTime, Exclude Time
Replies
2
Views
387
muhammad susanto
muhammad susanto
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
300
awoohaw
awoohaw
D
  • Solved
Issue with time formula
Replies
3
Views
108
KOKOSEK
KOKOSEK

Forum statistics

Threads
1,214,585
Messages
6,120,399
Members
448,957
Latest member
Hat4Life

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top