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:

[HH]:mm:ss

use formula:

=IF(E13=1,"",(A13+B13)-(A12+B12))

Autofill down to F595

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

=IF(AND($E12=1,HOUR($B12)=COLUMNS($I12:I12)),1/24*COLUMNS($I12:J12)-$B12,IF(AND($E12=0,HOUR($B12)=COLUMNS($I12:I12)),$B12-J$10,IF(AND($E12=1,HOUR($B12)<COLUMNS($I12:I12)),1/24,"")))

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.

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

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))