# Time w/in Each Hour of Day btwn a Time Range

1. ## Time w/in Each Hour of Day btwn a Time Range

I need a formula to calculate the time (in hours/fractions of an hour) that is "covered" for each hour of the day between a range of times. In other words, I have a "START TIME" and an "END TIME" and for each hour of the 24-hour clock, I want to know how much time this range covers.

For example, if my start time is 3:30am and my end time is 5:15am, for the 3am hour, the formula would return 0.5 hours, for the 4am hour it would return 1.0 hours, for the 5am hour it would return 0.25 hours, and for all other hours it would return 0.0 hours. The range of START TIME and END TIME can be up to 24 hours (but not more), but the tricky part is that the START TIME can be on the day BEFORE the END TIME (e.g., START TIME of 10:35pm and END TIME of 5:45pm the next day).

I've been able to come up with a formula in the past, but it is SOOOOO complicated. I'm hoping someone knows of a simple, clever way to formulate this.

Thanks!
George.

2. Hello,

Not sure but try:

assuming that the start time is in A2 and end time is in B2:
``Please Login or Register  to view this content.``
the formula calculates the time difference, cell format shoud be in h:mm

what do you mean by?
for the 3am hour, the formula would return 0.5 hours, for the 4am hour it would return 1.0 hours, for the 5am hour it would return 0.25 hours, and for all other hours it would return 0.0 hours.
if the time difference is > = 3hrs, it would return 0.5hrs, > = 4hrs would return 1?

3. ## Time w/in Each Hour of Day btwn a Time Range

OK....I *think* I understand what you're asking for...
and I think I figured out what you *need*.

Let's see if I'm right.
(I attached a demo file to this post)

Here's the set up:
A1: Test Date
B1: (a date to test coverage for....eg 12/20/2007)

A2: START
B2: END
C2:Z2....contains raw times formatted as "hh:mm":

C2: 12:00:00 AM......displays as 00:00
D2: 01:00:00 AM......displays as 01:00
etc....
Z2: 11:00:00 PM......displays as 23:00

A3: (a start date/time....eg 12/20/2007 1:00:00 AM)
B3: (an end date/time....eg 12/20/2007 5:15:00 AM)

Now for the calculations....

This formua calculates how much of the
hour beginning with the value in Row_2
and IN the date in B1 is covered by the range
in A3:B3, displayed as a decimal 0 through 1)

``Please Login or Register  to view this content.``
Copy that formula across through Z3

NOTE:
The calculations only pertain the portion of the
start through end date/time that are within the DATE in B1.

4. ## Making this harder than it is?

I use this on my billable hours spreadsheet: I use the TIMENOW() function (set to a macro key). Then, the format for TIME1 is [12/20/2007 4:06:36 pm] and TIME2 is [12/21/2007 2:06:36 AM]. Enter the formula =(TIME2-TIME1)*24 and presto!

5. ## ime w/in Each Hour of Day btwn a Time Range

I don't think the mathematical time difference is what the the poster is looking for.

also:
Originally Posted by BarryTheHatchet
I use this on my billable hours spreadsheet: I use the TIMENOW() function (set to a macro key). Then, the format for TIME1 is [12/20/2007 4:06:36 pm] and TIME2 is [12/21/2007 2:06:36 AM]. Enter the formula =(TIME2-TIME1)*24 and presto!
...and which version of Excel has the TIMENOW() function?

6. ## Thanks

I think Ron has the right method. I just returned to my comptuer and I'll try it and let you know.

Thanks!
George.

7. ## Modification Needed

Ron,

The code above did not seem to work if you had the start time on one day and the end time on the next. I modified it so that I THINK it works - I'll keep testing it. Here's the code I used:

=MIN((MAX(MIN(IF(\$A\$2>\$B\$2,IF(\$A\$2>D2,1+\$B\$1,\$B\$1),\$B\$1)+C2+1/24,\$B\$3),\$A\$3)-MIN(MAX(IF(\$A\$2>\$B\$2,IF(\$A\$2>D2,1+\$B\$1,\$B\$1),\$B\$1)+C2,\$A\$3),\$B\$3))*24,1)

Where A2 is the TIME portion of the Start Time and B2 is the TIME portion of the End Time. There might be a more elegant way to modify it, but this was the easiest for me within my skill set.

My spreadsheet is laid out a bit differently and I had to add an actual date to my times to use your code as is, so I included my spreadsheet here.

Thanks Again!!!
George.

8. ## Time w/in Each Hour of Day btwn a Time Range

Originally Posted by gbrogmus
Ron,

There might be a more elegant way to modify it...George.
Maybe....but, I couldn't come up with one! Nice job.

9. Originally Posted by gbrogmus
There might be a more elegant way to modify it
Hello George

Without using the dates, just referencing start and end times in B3 and C3, try this formula in G3 copied down

=((B\$3>C\$3)*MEDIAN(0,C\$3-E3,F3-E3)+MAX(0,MIN(F3,C\$3+(B\$3>C\$3))-MAX(E3,B\$3)))*24

10. ## Awesome!

I spent a few hours yesterday starting from scratch, trying to get a formula to work and the best I could come up with was:

=IF(\$B\$2=\$C\$2,1,IF(\$B\$2<\$C\$2,24*(MAX(MIN(F2,\$C\$2)-MAX(E2,\$B\$2),0)),MIN(MAX(24*(F2-\$B\$2),0),1)+MIN(MAX(24*(\$C\$2-E2),0),1)))

Your formula is much more elegant and it seems to work for every combination of times that I enter except for the same times (all 24 hours are covered - e.g., Start time of 1am and End time of 1am). That could easily be fixed with the first If statement I used above. That would result in the following formula:

=IF(\$B\$2=\$C\$2,1,((B\$3>C\$3)*MEDIAN(0,C\$3-E3,F3-E3)+MAX(0,MIN(F3,C\$3+(B\$3>C\$3))-MAX(E3,B\$3)))*24)

Which I still like better than the one I came up with!

Many Thanks!!!
George.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1