+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    12-20-2007
    Posts
    4

    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. #2
    Forum Contributor corinereyes's Avatar
    Join Date
    12-02-2003
    Location
    Philippines
    MS-Off Ver
    MS Excel 2010
    Posts
    509
    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?
    Last edited by corinereyes; 12-20-2007 at 06:10 PM.
    Corine

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    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.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    08-14-2007
    Posts
    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. #5
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    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:
    Quote 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. #6
    Registered User
    Join Date
    12-20-2007
    Posts
    4

    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. #7
    Registered User
    Join Date
    12-20-2007
    Posts
    4

    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.
    Attached Files Attached Files

  8. #8
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

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

    Quote 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. #9
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Quote 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. #10
    Registered User
    Join Date
    12-20-2007
    Posts
    4

    Awesome!

    Daddylonglegs,

    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.
    Last edited by gbrogmus; 12-30-2007 at 03:05 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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