Time calculation run hours per day

[PaulWAdams]

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:

[TABLE="width: 500"]
<tbody style="border-collapse: collapse; width: auto;">[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]date[/TD]
[TD="align: center"]GT1 online[/TD]
[TD="align: center"]GT2 online[/TD]
[TD="align: center"]ST3 online[/TD]
[TD="align: center"]Total Plant online[/TD]
[TD="align: center"]GT1 bkr shut[/TD]
[TD="align: center"]GT1 bkr open[/TD]
[TD="align: center"]GT2 bkr shut[/TD]
[TD="align: center"]GT2 bkr open[/TD]
[TD="align: center"]ST3 bkr shut[/TD]
[TD="align: center"]ST3 bkr open[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]15.62[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]11/03/19 10:01:00[/TD]
[TD="align: center"]11/03/19 00:48:00[/TD]
[TD="align: center"]11/01/19 06:01:00[/TD]
[TD="align: center"]11/01/19 21:38:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]11/05/19 22:41:00[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]17.98[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]11/04/19 07:01:00[/TD]
[TD="align: center"]11/03/19 21:52:00[/TD]
[TD="align: center"]11/02/19 06:01:00[/TD]
[TD="align: center"]11/05/19 22:41:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]15.65[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"][/TD]
[TD="align: center"]11/05/19 22:41:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]16.98[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]22.68[/TD]
[TD="align: center"]22.68[/TD]
[TD="align: center"]22.68[/TD]
[TD="align: center"]22.68[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Fergus]

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]

Here's another option:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	date	GT1 online	GT2 online	ST3 online	Total Plant online	GT1 bkr shut	GT1 bkr open	GT2 bkr shut	GT2 bkr open	ST3 bkr shut	ST3 bkr open
2	11/1/2019	24.00	15.62	24	24	11/3/2019 10:01	11/3/2019 0:48	11/1/2019 6:01	11/1/2019 21:38	1/1/2019	11/5/2019 22:41
3	11/2/2019	24.00	17.98	24	24	11/4/2019 7:01	11/3/2019 21:52	11/2/2019 6:01	11/5/2019 22:41
4	11/3/2019	12.65	24.00	24	24		11/5/2019 22:41
5	11/4/2019	16.98	24.00	24	24
6	11/5/2019	22.68	22.68	22.6833	22.68
Sheet5

Cell Formulas
Range	Formula
B2	=(SUMIFS(G:G,G:G,">="&A2,G:G,"<"&A3)-SUMIFS(F:F,F:F,">="&A2,F:F,"<"&A3)-IF(MAXIFS(F:F,F:F,"<"&A2)+MAXIFS(G:G,G:G,"<"&A2),MAXIFS(F:F,F:F,"<"&A2)>MAXIFS(G:G,G:G,"<"&A2),MIN(F:F)>MIN(G:G))*A2+IF(MAXIFS(F:F,F:F,"<"&A3)+MAXIFS(G:G,G:G,"<"&A3),MAXIFS(F:F,F:F,"<"&A3)>MAXIFS(G:G,G:G,"<"&A3),MIN(F:F)>MIN(G:G))*A3)*24
C2	=(SUMIFS(I:I,I:I,">="&A2,I:I,"<"&A3)-SUMIFS(H:H,H:H,">="&A2,H:H,"<"&A3)-IF(MAXIFS(H:H,H:H,"<"&A2)+MAXIFS(I:I,I:I,"<"&A2),MAXIFS(H:H,H:H,"<"&A2)>MAXIFS(I:I,I:I,"<"&A2),MIN(H:H)>MIN(I:I))*A2+IF(MAXIFS(H:H,H:H,"<"&A3)+MAXIFS(I:I,I:I,"<"&A3),MAXIFS(H:H,H:H,"<"&A3)>MAXIFS(I:I,I:I,"<"&A3),MIN(H:H)>MIN(I:I))*A3)*24
D2	=(SUMIFS(K:K,K:K,">="&A2,K:K,"<"&A3)-SUMIFS(J:J,J:J,">="&A2,J:J,"<"&A3)-IF(MAXIFS(J:J,J:J,"<"&A2)+MAXIFS(K:K,K:K,"<"&A2),MAXIFS(J:J,J:J,"<"&A2)>MAXIFS(K:K,K:K,"<"&A2),MIN(J:J)>MIN(K:K))*A2+IF(MAXIFS(J:J,J:J,"<"&A3)+MAXIFS(K:K,K:K,"<"&A3),MAXIFS(J:J,J:J,"<"&A3)>MAXIFS(K:K,K:K,"<"&A3),MIN(J:J)>MIN(K:K))*A3)*24

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]

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.