Formula that counts amount of hour in each 24 hours

Hi all,

I have a excel file that shows different time and dates power is turned on or off.
I would like to get amount of hour that the power is on during each 24 hours.

for example the data could be like below:
Jan 1, 2011 1:20 AM Turned ON
Jan 1, 2011 2:30 AM Turned OFF
Jan 5, 2011 1:00 AM Turned ON
Jan 6, 2011 3:00 PM Turned OFF

I am trying to get something like below:
12:00AM-12:59AM: 0 hours
1:00AM-1:59AM: 02:40:00
2:00AM-2:59AM: 02:30:00
3:00AM-3:59AM: 02:00:00
.
.
.
etc


I have been working on this for a while.
Now I got a really really long nested if cases.
Is there any better, easier and shorter way to get what I am looking for?

Here is the example file in attached.


Thank you very very very much!

Start in cell F13

Format cell as:

Please Login or Register  to view this content.

use formula:

Please Login or Register  to view this content.

Autofill down to F595

Hello stewegg,

I put this formula in I11 and copied it down and across to AF595

=IF($E11,($B11>$B12)*MEDIAN(0,$B12-I$10,1/24)+MAX(0,MIN(I$10+1/24,$B12+($B11>$B12))-MAX(I$10,$B11))+INT($A12+$B12-$A11-$B11)/24,"")

That will give you a total within each hourly range for each "ON" row

Then there is a sum at the bottom of each column for the total hours in that period, so F599 gives you the hours between midnight and 01:00, G599 gives you hours between 01:00 and 02:00 etc.....

Notice that the sum in AG599 is 4928:05:24 for the total ON hours - this matches the figure in B3 which I calculated directly from the data in columns A and B, see attached

Originally Posted by Cutter

This isn't fully tested nor complete because I don't really understand what you're asking when you say there are 2 hours between 1:00:00 AM and 1:59:59 AM - seems to me that would be 1 hour.

But maybe you can use this. I'm assuming you want something in the cells below the hour headings I10:AG10 to indicate ON times broken down for each hour.

With that in mind place this formula in J12 and drag over to AF12 and down

Please Login or Register  to view this content.

The first and last columns will have to be modified but as this may not be what you want I won't do any more now. Again - NOT FULLY TESTED

Hi Cutter,

Thank you very much for your help.

Obviously with your problem you have some "ON" periods that are longer than 24 hours - clearly in those cases then for every 24 hours each hour must be covered once, so this part counts those whole hours for whole days

=INT($A12+$B12-$A11-$B11)/24

and then the rest of the formula I used is a variation on an old formula I developed some time ago for calculating hours between 2 times, i.e.

=(A2>B2)*MEDIAN(0,B2-start,end-start)+MAX(0,MIN(end,B2+(A2>B2))-MAX(start,A2))

so if you have a start time in A2 and an end time in B2, e.g. 08:00 and 16:00 then the formula will calculate how many hours of that shift are between the start and end defined.

I could probably explain that in more detail but it's a formula that has been "distilled" over a period of time...and I'd have to think about the logic a little....

It's designed to cope with any shift length up to 24 hours, even if that period crosses midnight like 20:00 to 07:00 so it works well on your data, looking at times only given that the whole days have already been accounted for.

You could even extend the formula to calculate the entire ON time for each hour in a single formula, e.g. with this formula in J11, confirmed with CTRL+SHIFT+ENTER and copied down, see revised attachment

=SUM(IF(E$12:E$595,(B$12:B$595>B$13:B$596)*IF(B$13:B$596-H11>0,IF(B$13:B$596-H11>1/24,1/24,B$13:B$596-H11),0)+TEXT(IF(H11+1/24<B$13:B$596+(B$12:B$595>B$13:B$596),H11+1/24,B$13:B$596+(B$12:B$595>B$13:B$596))-IF(H11>B$12:B$595,H11,B$12:B$595),"[h]:mm:ss;""0"";""0""")+INT(A$13:A$596+B$13:B$596-A$12:A$595-B$12:B$595)/24))

Hi daddylonglegs,

I understand the part with INT($A12+$B12-$A11-$B11)/24

But, could you explain the other part more in detail?

($B12>$B13)*MEDIAN(0,$B13-I$10,1/24)+
I know this part is checking for overnight.
But why MEDIAN between 0, $B13-I$10 and 1/24?

MAX(0,MIN(I$10+1/24,$B13+($B12>$B13))-MAX(I$10,$B12))
This part, I really don't understand.....



Thank you very much for your time.

Hello stewegg, OK, as briefly as i can......

Obviously if you have a start time and end time that might go through midnight then you might need to add some time from "day 1" and also some time from "day 2", e.g. if we are checking the period 05:00 - 06:00 and you have an "ON" time that starts at 05:45 and ends (on another day) at 05:20 then you need to include 15 minutes from the start of the day (05:45 - 06:00) and also 20 minutes at the end (05:00 - 05:20).

This part

=($B12>$B13)*MEDIAN(0,$B13-I$10,1/24)

gets the time on day 2 only (if there is a day 2), so in my example above that would be

=(05:45>05:20)*MEDIAN(0,05:20-05:00,1/24)

The first part equates to TRUE and when multiplied that becomes a 1, i.e.

=(1)*MEDIAN(0,$B13-I$10,1/24)

using MEDIAN with 3 values always returns the middle value of the 3, so in this case the largest value is 1/24 = 1 hour, the smallest is zero so the median is 0:20 and that is the value that is returned (as required)

Clearly if B13 is before 05:00 then B13-I10 returns a negative value so the median is therefore 0.....if B13 is greater than 06:00 then the median value is 1/24 = 1:00....so the MEDIAN has the effect of putting an upper and lower limit on the subtraction, the result must always be between 0 and 1 hour.

If B12 is < B13 (i.e. start and end time are on the same day) then the test returns FALSE which co-erces to zero and the MEDIAN is now redundant, the whole thing returns zero.

So for the time on day 1 we have

=MAX(0,MIN(I$10+1/24,$B13+($B12>$B13))-MAX(I$10,$B12))

Note that there may be a day 2, there may not be

In general terms it would be sufficient to do a calculation that would subtract the greater value of the start time of your 1 hour period (I10) and the start of your ON period (B12) from the smaller value out of the end time of your 1 hour period (I10+1/14) and the end time of your ON period (B13)....so that would be simply

=MIN(I$10+1/24,$B13)-MAX(I$10,$B12)

but if B12 > B13 then B13 is effectively on day 2 so we need to add 1 to simulate that so that gives us this:

=MIN(I$10+1/24,$B13+($B12>$B13))-MAX(I$10,$B12)

In my example above, then, that would become

=MIN(06:00,05:20+1)-MAX(05:00,05:45)

=06:00-5:45

=0:15

so that returns the 15 minutes as required........but let's look at a scenario where our start time is 06:45 rather than 05:45, the MAX part would return 06:45 and therefore our calculation would return a negative value....which is why our MIN-MAX calculation is wrapped in a MAX function with zero, i.e.

=MAX(0,MIN(I$10+1/24,$B13+($B12>$B13))-MAX(I$10,$B12))

the MAX(0,......part ensures we don't get a negative value.

In the longer formula I posted which calculates all the rows in one go you can't use MAX, MIN and MEDIAN so those have to be replaced with longer IF formulas, e.g. MAX(0,x) where x is some calculation, needs to be replaced with

=IF(x>0,x,0)

regards, daddylonglegs

mmm....

everything I wrote above is valid but having thought about it some more there's a better approach if I adapt another technique I use elsewhere. Using that approach you can use this formula in I12 copied across and down

=IF($E12,MEDIAN($B13,I$10,I$10+1/24)-MEDIAN($B12,I$10,I$10+1/24)+INT($A13-$A12)/24,"")

and for a single cell version In I5 copied across

=SUM(IF($E12:$E595,IF($B13:$B596>I$10+1/24,I$10+1/24,IF($B13:$B596<I$10,I$10,$B13:$B596))-IF($B12:$B595>I$10+1/24,I$10+1/24,IF($B12:$B595<I$10,I$10,$B12:$B595))+INT($A13:$A596-$A12:$A595)/24))

confirmed with CTRL+SHIFT+ENTER

see attached

Hi daddylonglegs,

Thank you, I have understand it now.

But, what about for the case such as listed below?
Jan 18th 12:30AM Turned ON
Jan 19th 1:59 AM Turned OFF

In this case, it's over one day but B12<B13.

With the formula below for 1:00am-1:59:59am.
=MIN(I$10+1/24,$B13+($B12>$B13))-MAX(I$10,$B12)
=MIN(2am, 1:59am+(0)) - MAX(1am,12:30am)
=1:59am - 1am
=59 mins
Where it should be 1hr 59mins in total.
Isn't it?


Thank you very much for your time.

Hello stewegg, please don't quote whole posts, if you want to address a specific part then please just edit to just leave that part - I've edited your post......

Yes that's correct. The extra hour is delivered by this part

=INT($A12+$B12-$A11-$B11)/24

because the period is more than 1 day INT here returns 1 and you get 1 hour from there. The rest of the formula never returns more than 1 hour

You might have noticed that I suggested another approach above - I think they both work but I recommend the second one as it's a little shorter and simpler........but possibly still tricky to explain

That previous formula, I noticed, was returning some values that were a very small value less than zero, so I'd fix that with an additional MAX function like this

=IF($E12,MAX(MEDIAN($B13,I$10,I$10+1/24)-MEDIAN($B12,I$10,I$10+1/24)+INT($A13-$A12)/24,0),"")

In your data it looks like all the ON periods at weekends are self contained within the weekend, i.e. they never cross from Friday into Saturday or Sunday into Monday, so it would be sufficient to modify the above formula according to the weekday of the start date, i.e.

=IF($E12,IF(WEEKDAY($A12,2)<6,MAX(MEDIAN($B13,I$10,I$10+1/24)-MEDIAN($B12,I$10,I$10+1/24)+INT($A13-$A12)/24,0),0),"")

That will exclude weekend hours. If you want weekend hours only then change the <6 to a >5

If you might have ON periods that cross from weekday to weekend or vice versa (and therefore you might need to split the hours for single ON periods) then this formula will exclude weekends

=IF($E12,MAX((NETWORKDAYS($A12,$A13)-1)/24+IF(NETWORKDAYS($A13,$A13),MEDIAN($B13,I$10,I$10+1/24),I$10+1/24)-MEDIAN($B12*NETWORKDAYS($A12,$A12),I$10,I$10+1/24),0),"")

for your current data the last 2 should give the same results

The single cell versions for excluding weekends would be this to just exclude on the basis of start date

=SUM(IF($E12:$E595,IF(WEEKDAY($A12:$A595,2)<6,IF($B13:$B596>I$10+1/24,I$10+1/24,IF($B13:$B596<I$10,I$10,$B13:$B596))-IF($B12:$B595>I$10+1/24,I$10+1/24,IF($B12:$B595<I$10,I$10,$B12:$B595))+INT($A13:$A596-$A12:$A595)/24)))

or this one to take into account ON periods crossing from weekend into weekday or vice versa

=SUM(IF($E12:$E595,(NETWORKDAYS($A12:$A595+0,$A13:$A596+0)-1)/24+IF(NETWORKDAYS($A13:$A596+0,$A13:$A596+0),IF($B13:$B596>I$10+1/24,I$10+1/24,IF($B13:$B596<I$10,I$10,$B13:$B596)),I$10+1/24)-IF(NETWORKDAYS($A12:$A595+0,$A12:$A595+0),IF($B12:$B595>I$10+1/24,I$10+1/24,IF($B12:$B595<I$10,I$10,$B12:$B595)),I$10)))

Either way confirm with CTRL+SHIFT+ENTER

That's absolutely the way I would do it......but I note that your holiday range is different in the last NETWORKDAYS function, obviously they should all be the same.

Are you trying to exclude a single range of dates or multiple ranges? You say March 1st to May 1st, is that in a single (specific) year or in any year?

For now, single range it is.
That would be any year through out the period.
Also, it would be nice if the range can go from first year into the second year.
ie December of x year to March of x+1 year.