# Calculating Downtime

#### mrpoo

##### New Member
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 1 24/06/2021 12:46:13 141.6 Tuner 2 24/06/2021 12:47:54 152.5 Tuner 1 24/06/2021 12:53:12 140.2 Tuner 2 24/06/2021 12:53:43 152.5 Tuner 1 24/06/2021 13:35:26 141.5 Tuner 1 24/06/2021 13:35:47 151.4

A C E G I K

#### Attachments

• 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.

##### Active Member
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
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
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

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

Replies
1
Views
120
Replies
1
Views
53
Replies
12
Views
263
Replies
8
Views
122
Replies
1
Views
119

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.

### Which adblocker are you using?

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

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