Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    West Seneca, NY
    Posts
    150
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Time duration in Excel 2007

    In calculating time durations some results might be less than six hours while others can be greater than 6 hours. How can I express those duration that are equal or greater than 6 hours long by subtracting 30 mins of time from the total using an ď=IF ď statement? Or is there an another method?
    Example:
    A2=9:00 am B2= 1:00 PM resulting in a total of 4:00 hrs in C2 (B2-A2). However the next entry may be
    A3=8:00 am B3=4:00 PM resulting in a total of 8:00 hrs in C3 (B3-A3). Since itís more than 6 hrs I want to deduct 30 minutes to show 7hrs 30 min.
    Bear in mind the times can be anything from 1 to 15 hours long for any entry.



    Any suggestions will be greatly appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =B2-A2-((B2-A2)>6/24)*0.5/24

    Or with one of the variations with an if:
    =B2-A2-IF((B2-A2)>6/24,0.5,0)/24

    Note: 6/24 is 6 hours and 0.5/24 is a half-hour since time in excel is in units of DAYS.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Attached is another example
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    West Seneca, NY
    Posts
    150
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    =B2-A2-((B2-A2)>6/24)*0.5/24

    Or with one of the variations with an if:
    =B2-A2-IF((B2-A2)>6/24,0.5,0)/24

    Note: 6/24 is 6 hours and 0.5/24 is a half-hour since time in excel is in units of DAYS.

    Steve
    Steve, Thank you. The key is that I did not understand or realize the 6/24 and .5/24.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    West Seneca, NY
    Posts
    150
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I like your layout and it gave me several other ideas on how to present my data.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The key is that I did not understand or realize the 6/24 and .5/24
    This is a key point in understanding dates and times in Excel. Dates and times in excel are all based on units of DAYS, so the integer portion of a date/time defines the day (the number of days past 1/1/1900). The decimal portion of the number is the fraction of a day. 0.25 = (6/24) is 6 AM, 0.5 (12/24) is noon, 0/75 (18/24) is 6PM, etc

    This system allows subtraction to determine the number of days between 2 date/times. This allows using the builtin excel date/time functions. Or if you want to use decimal numbers, you can multiply the difference by 24 to get hours, by 24*60 to get minutes, etc.

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    West Seneca, NY
    Posts
    150
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Since I don't use the time functions very often I know I won't remember any of this in the near future. I'm going to save the replies for future reference.

    Thanks again to you and the others who posted.

  8. #8
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Is there a way to add seconds to be calculated by the formula as well?

    I am trying to calculate the duration between start and end time of running some reports.

Posting Permissions

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