Time calculation run hours per day

PaulWAdams

New Member
Joined
Nov 13, 2019
Messages
2
I need help to get a calculation of run hours per day for 3 units, based on generator breaker shut/open times. We currently use an integration over time to count the time for the breakers closed, but this gets messed up when the computer is rebooted or updated. I need to calculate the decimal run time hours for each of 3 units for each calendar day- with a time that may have started with bkr closed from previous month, until output bkr finally opens within the current month, sometimes several days later. Sometimes we shutdown a unit after midnight, and start it back up in the morning, and so have 2 separate run times within the same day. Our current spreadsheet looks something like this: columns FGHIJK are direct times that generator output bkrs are shut or opened, and BCDE are the daily runtime hours for each unit. The integrated times in B-E can be off by an hour or so on some days due to other activity on the computer- and we need the runtime hours calculated each day. I usually have to manually resolve conflicts by working out the daily hours from the breaker close/open times, but need help breaking it up for the separate days with something close to this table:

ABCDEFGHIJK
1dateGT1 onlineGT2 onlineST3 onlineTotal Plant onlineGT1 bkr shutGT1 bkr openGT2 bkr shutGT2 bkr openST3 bkr shutST3 bkr open
212415.62242411/03/19 10:01:0011/03/19 00:48:0011/01/19 06:01:0011/01/19 21:38:0011/05/19 22:41:00
322417.98242411/04/19 07:01:0011/03/19 21:52:0011/02/19 06:01:0011/05/19 22:41:00
4315.6524242411/05/19 22:41:00
5416.98242424
6522.6822.6822.6822.68

<tbody style="border-collapse: collapse; width: auto;">
</tbody>
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hello and welcome to MrExcel

There are a couple of issues with this. The value in column A should probably be a date (e.g. 1 Nov 2019) and secondly the non-normalised data in column F:K make this relatively difficult. However, I do have a formula that will work out the "on" time for each day (assuming column A contains a date value). This also assumes the data in columns F:K have both the date and time as you have shown.

It will not work if there are more than 2 "shut" or "open" entries for one day (it can handle at most entries 2 per day). If you have 3 or more then you may want to manually calculate the on time. This assumes that if there are no entries in either the open or close column then the breaker was assumed to be closed at the start of the month.

The following formula is an array formula, entered into cell B3 being the first entry for a month for GT1 (where the days date is in A3) and can be copied down. Being an array formula you need to confirm it using Ctrl+Shift+Enter after you have entered it. You will know you have done it correctly when curly brackets { } appear automatically around the formula. The reference to F1:F10 is for GT1 close (change to suit) and G1:G10 is for GT1 open (also change to suit).

=IF(OR(MAX(IF(G$1:G$10<$A3, G$1:G$10,0))>MAX(IF(F$1:F$10<$A4, F$1:F$10, 0)), AND(MIN(F$1:F$10)>=$A4, MIN(G$1:G$10)<$A3)), 0, (MIN($A4, MIN(IF(G$1:G$10>=$A3, G$1:G$10, 8^8)))-MAX($A3, MAX(IF(F$1:F$10<$A4, F$1:F$10, 0))))*24)+IFERROR((INDEX(F$1:F$10, MATCH(A3, INT(F$1:F$10),0))>INDEX(G$1:G$10, MATCH(A3, INT(G$1:G$10),0)))*24, 0)

This is broken into 3 parts:
the first 1/3rd up to the part "<$A3)), 0," is working out if the time should be zero for the day.
the second part from "0, (MIN($A4," to ")*24)" is taking the min of (end of day, off time) less max of (start of day, on time) to work out the number of hours on per day
the third part from "IFERROR" adds 24 where the on time is after the off time (it is correcting an error in part 2).

I trust this helps.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,767
Here's another option:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">date</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">GT1 online</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">GT2 online</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">ST3 online</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">Total Plant online</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">GT1 bkr shut</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">GT1 bkr open</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">GT2 bkr shut</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">GT2 bkr open</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">ST3 bkr shut</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">ST3 bkr open</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/1/2019</td><td style="text-align: right;;">24.00</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">15.62</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">24</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">24</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/3/2019 10:01</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/3/2019 0:48</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/1/2019 6:01</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/1/2019 21:38</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">1/1/2019</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/5/2019 22:41</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/2/2019</td><td style="text-align: right;;">24.00</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">17.98</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">24</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">24</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/4/2019 7:01</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/3/2019 21:52</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/2/2019 6:01</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/5/2019 22:41</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/3/2019</td><td style="text-align: right;;">12.65</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">24.00</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">24</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">24</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/5/2019 22:41</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/4/2019</td><td style="text-align: right;;">16.98</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">24.00</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">24</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">24</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">11/5/2019</td><td style="text-align: right;;">22.68</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">22.68</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">22.6833</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">22.68</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=(<font color="Blue">SUMIFS(<font color="Red">G:G,G:G,">="&A2,G:G,"<"&A3</font>)-SUMIFS(<font color="Red">F:F,F:F,">="&A2,F:F,"<"&A3</font>)-IF(<font color="Red">MAXIFS(<font color="Green">F:F,F:F,"<"&A2</font>)+MAXIFS(<font color="Green">G:G,G:G,"<"&A2</font>),MAXIFS(<font color="Green">F:F,F:F,"<"&A2</font>)>MAXIFS(<font color="Green">G:G,G:G,"<"&A2</font>),MIN(<font color="Green">F:F</font>)>MIN(<font color="Green">G:G</font>)</font>)*A2+IF(<font color="Red">MAXIFS(<font color="Green">F:F,F:F,"<"&A3</font>)+MAXIFS(<font color="Green">G:G,G:G,"<"&A3</font>),MAXIFS(<font color="Green">F:F,F:F,"<"&A3</font>)>MAXIFS(<font color="Green">G:G,G:G,"<"&A3</font>),MIN(<font color="Green">F:F</font>)>MIN(<font color="Green">G:G</font>)</font>)*A3</font>)*24</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=(<font color="Blue">SUMIFS(<font color="Red">I:I,I:I,">="&A2,I:I,"<"&A3</font>)-SUMIFS(<font color="Red">H:H,H:H,">="&A2,H:H,"<"&A3</font>)-IF(<font color="Red">MAXIFS(<font color="Green">H:H,H:H,"<"&A2</font>)+MAXIFS(<font color="Green">I:I,I:I,"<"&A2</font>),MAXIFS(<font color="Green">H:H,H:H,"<"&A2</font>)>MAXIFS(<font color="Green">I:I,I:I,"<"&A2</font>),MIN(<font color="Green">H:H</font>)>MIN(<font color="Green">I:I</font>)</font>)*A2+IF(<font color="Red">MAXIFS(<font color="Green">H:H,H:H,"<"&A3</font>)+MAXIFS(<font color="Green">I:I,I:I,"<"&A3</font>),MAXIFS(<font color="Green">H:H,H:H,"<"&A3</font>)>MAXIFS(<font color="Green">I:I,I:I,"<"&A3</font>),MIN(<font color="Green">H:H</font>)>MIN(<font color="Green">I:I</font>)</font>)*A3</font>)*24</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=(<font color="Blue">SUMIFS(<font color="Red">K:K,K:K,">="&A2,K:K,"<"&A3</font>)-SUMIFS(<font color="Red">J:J,J:J,">="&A2,J:J,"<"&A3</font>)-IF(<font color="Red">MAXIFS(<font color="Green">J:J,J:J,"<"&A2</font>)+MAXIFS(<font color="Green">K:K,K:K,"<"&A2</font>),MAXIFS(<font color="Green">J:J,J:J,"<"&A2</font>)>MAXIFS(<font color="Green">K:K,K:K,"<"&A2</font>),MIN(<font color="Green">J:J</font>)>MIN(<font color="Green">K:K</font>)</font>)*A2+IF(<font color="Red">MAXIFS(<font color="Green">J:J,J:J,"<"&A3</font>)+MAXIFS(<font color="Green">K:K,K:K,"<"&A3</font>),MAXIFS(<font color="Green">J:J,J:J,"<"&A3</font>)>MAXIFS(<font color="Green">K:K,K:K,"<"&A3</font>),MIN(<font color="Green">J:J</font>)>MIN(<font color="Green">K:K</font>)</font>)*A3</font>)*24</td></tr></tbody></table></td></tr></table><br />

This is not an array formula, but it requires the MAXIFS function, which is only available in newer versions of Excel. It can be rewritten without it, but it would become an array formula. Another advantage of this formula is that there is no limit on the number of times the breakers can be opened or closed per day.

And I also echo some of the issues that Andrew raised. Probably 2/3 of the formula is figuring out whether the machine is on or off at the start of the day. And even with that, I had to add a dummy date in J2 to make it work for ST3.

Also, notice that I put a full date in column A, which is also necessary.

The B, C, and D are essentially the same, I just had to change the column references. I could adapt it so you could copy across as well as down, but it would make it more complicated and use OFFSET, which I'd rather not. We could also rearrange the sheet a bit to make that work.

The B4 value is different from your example, but I believe this value to be correct.

Anyway, let us know if this helps.
 

PaulWAdams

New Member
Joined
Nov 13, 2019
Messages
2
Thanks much for the answers. The date is actually full but I was just filling out the post off the top of my head, sorry.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,363
Messages
5,468,184
Members
406,569
Latest member
Quest_

This Week's Hot Topics

Top