+ Reply to Thread
Results 1 to 12 of 12

Excel 2007 : Specific time period calculation from a time range

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-18-2011
    Location
    Jeddah
    MS-Off Ver
    Excel 2007
    Posts
    362

    Question Specific time period calculation from a time range

    From the attached sheet, I would like help please in a formula to extract time value for times that falls between 18:00 and 0600 next day from cells A and B and have the result in Column D cells. as for the attached example, the requested formula should result in 4:05 in cell D1, and 5:42 in cell D2.
    Attached Files Attached Files
    Last edited by Khaldon; 05-20-2011 at 11:06 AM.

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Specific time period calculation from a time range

    I'm sure there's a better way but try:

    =IF(HOUR(A2)<6,6/24-(A2-INT(A2)),0)+IF(HOUR(B2)>17,B2-INT(B2)-18/24,0)
    Last edited by Cutter; 05-18-2011 at 05:39 PM. Reason: Amended formula

  3. #3
    Forum Contributor
    Join Date
    04-18-2011
    Location
    Jeddah
    MS-Off Ver
    Excel 2007
    Posts
    362

    Re: Specific time period calculation from a time range

    Thank you for the formula, however I have three comments if I may:
    First: The times results are not correct when both the starting and end times in A and B cells happen to fall between 18:00 and 06:00.
    Second: 6:00 will result when no time values are in cell A and B instead of blank!
    Thirdly: 0 will result if both starting and ending times falls outside 18:00 and 6:00 instead of blank.
    Please see new attached sheet.
    Attached Files Attached Files

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Specific time period calculation from a time range

    I didn't test the formula I gave you beyond the 2 examples you provided.

    Try this amended formula:

    =IF(B6="","",IF(OR(AND(HOUR(A6)<6,B6<=INT(A6)+6/24),AND(HOUR(A6)>17,B6>INT(A6)+18/24)),B6-A6,IF(HOUR(A6)<6,6/24-(A6-INT(A6)),0)+IF(HOUR(B6)>17,B6-INT(B6)-18/24,0)))
    AND
    to blank a result of 0 change the custom format from [h]:mm to [h]:mm;;""
    Last edited by Cutter; 05-19-2011 at 08:26 AM. Reason: Amended formula

  5. #5
    Forum Contributor
    Join Date
    04-18-2011
    Location
    Jeddah
    MS-Off Ver
    Excel 2007
    Posts
    362

    Re: Specific time period calculation from a time range

    Well done, please correct me if I was wrong, I did a little of modification to your formula, I changed all the 17 numbers to 18 in the formula which I think it was missed typed.
    Last edited by Khaldon; 05-19-2011 at 07:13 PM.

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Specific time period calculation from a time range

    No, I meant it to be 17.
    Have you tested it with times from 18:00 to 18:59?

  7. #7
    Registered User
    Join Date
    05-16-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Specific time period calculation from a time range

    Pardon my ignorance guys, but isn't the difference between 4:40 PM and 10:05 PM on the same day a total of 5 hours and 25 minutes?

    I am delaing with a similar excel problem and came upon this thread. Cutter, I am trying to figure out if your solutiong might help with my issue. Thanks, and sorry for the intromision.

    Start End Diff Expected
    04/01/2011 16:40 04/01/2011 22:05 5:25 4:05
    05/01/2011 00:18 05/01/2011 08:42 8:24 5:42

  8. #8
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Specific time period calculation from a time range

    @Armandog
    The OP in this thread doesn't want
    the difference between 4:40 PM and 10:05 PM on the same day
    he/she wants
    to extract time value for times that falls between 18:00 and 0600 next day
    or the total time that was NOT between 6:00 and 18:00 of the same day.

+ 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