Calculating Downtime

mrpoo

New Member
Joined
Apr 12, 2018
Messages
13
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: 1

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
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.
 

mrpoo

New Member
Joined
Apr 12, 2018
Messages
13
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.
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?
 

mrpoo

New Member
Joined
Apr 12, 2018
Messages
13
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")
 

mrpoo

New Member
Joined
Apr 12, 2018
Messages
13
provide minisheet please.

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")
On the Data sheet the info is in the following:

Tuner ID - C
Test Date - I
Test Time - J
Cycle Time - FD
 

Forum statistics

Threads
1,141,062
Messages
5,704,054
Members
421,325
Latest member
tapete86

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
Top