+ Reply to Thread
Results 1 to 7 of 7

formula to calculate time difference crossing midnight

  1. #1
    ditorejax
    Guest

    formula to calculate time difference crossing midnight

    In Excel I have been trying to find an easier way to calculate a time
    difference where the times cross midnight. Example:
    Start time: 23:50:00
    End time: 00:15:00

    How would you formulate an equation to determine the duration of time or
    differnce between the start and end time?

  2. #2
    Richard Buttrey
    Guest

    Re: formula to calculate time difference crossing midnight

    On Thu, 17 Aug 2006 07:16:03 -0700, ditorejax
    <ditorejax@discussions.microsoft.com> wrote:

    >In Excel I have been trying to find an easier way to calculate a time
    >difference where the times cross midnight. Example:
    >Start time: 23:50:00
    >End time: 00:15:00
    >
    >How would you formulate an equation to determine the duration of time or
    >differnce between the start and end time?


    One way which results in hours and decimal of an hour is

    =IF((end-start)<0,(end-start)*24+24,(end-start)*24)

    If you want to see hours and minutes you'd need to modify it to pick
    up the decimal fraction and multiply it by 60.

    HTH


    __
    Richard Buttrey
    Grappenhall, Cheshire, UK
    __________________________

  3. #3
    Marcelo
    Guest

    RE: formula to calculate time difference crossing midnight

    Hi,

    one way is add 1 in 00:15:00

    assuming that 23:50:00 is on A2 and 00:15:00 is on A3
    =a3+1-a2

    =00:25:00

    hth
    --
    regards from Brazil
    Thanks in advance for your feedback.
    Marcelo



    "ditorejax" escreveu:

    > In Excel I have been trying to find an easier way to calculate a time
    > difference where the times cross midnight. Example:
    > Start time: 23:50:00
    > End time: 00:15:00
    >
    > How would you formulate an equation to determine the duration of time or
    > differnce between the start and end time?


  4. #4
    Sloth
    Guest

    RE: formula to calculate time difference crossing midnight

    =(A2>A3)+A3-A2

    1 only needs to be added if A2>A3 otherwise you get 24 extra hours when
    times don't go over midnight. You will see the problem when you start
    summing the times and if you format as [h]:mm:ss

    "Marcelo" wrote:

    > Hi,
    >
    > one way is add 1 in 00:15:00
    >
    > assuming that 23:50:00 is on A2 and 00:15:00 is on A3
    > =a3+1-a2
    >
    > =00:25:00
    >
    > hth
    > --
    > regards from Brazil
    > Thanks in advance for your feedback.
    > Marcelo
    >
    >
    >
    > "ditorejax" escreveu:
    >
    > > In Excel I have been trying to find an easier way to calculate a time
    > > difference where the times cross midnight. Example:
    > > Start time: 23:50:00
    > > End time: 00:15:00
    > >
    > > How would you formulate an equation to determine the duration of time or
    > > differnce between the start and end time?


  5. #5
    Registered User
    Join Date
    01-24-2013
    Location
    NC, US
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: formula to calculate time difference crossing midnight

    Thanks for all the help! I've been calculating these crossing midnight durations manually to-date and it's laborious and error-prone.

    To recap and combine the advice, the below formula can take care of a duration calculation both for cases where a duration crosses midnight and where it does not cross midnight. The specific row #s and column #s would need to be modified to suit your data. The extra parentheses when subtracting the begin time from the end time +1 may not be needed, but it makes the formula clearer to me.

    - Formula using the convention other posters have used where the end time is in the row below the begin time:

    =IF(A3<A2,(A3+1)-(A2),(A3-A2))

    - Formula using a different convention where column A contains the begin time and column B contains the end time. This formula suits my data:

    =IF(B2<A2,(B2+1)-(A2),(B2-A2))


    Note, however, that a different formula or manual efforts may be needed if you are crossing over more than one day. In that case, you might need to combine/concatenate the day and time together, but I don't have this condition in my data so I have not tried to account for it. Just a heads-up for those that have this condition.
    Last edited by Indiver716; 01-24-2013 at 11:02 AM. Reason: Did not originally follow the original poster's column and row convention.

  6. #6
    Registered User
    Join Date
    06-25-2014
    Location
    Oregon, USA
    MS-Off Ver
    2013
    Posts
    2

    Re: formula to calculate time difference crossing midnight

    Thank you I was dealing with the same situation, and this formula seems to work perfectly.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: formula to calculate time difference crossing midnight

    One way way...

    A1 = 8:00 PM
    B1 = 3:00 AM

    =MOD(B1-A1,1)

    Format as h:mm
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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