Closed Thread
Results 1 to 15 of 15

Calculating time difference over midnight!

  1. #1
    Registered User
    Join Date
    09-29-2005
    Posts
    1

    Calculating time difference over midnight!

    I have read all of the tips and tricks to try to get an answer without bothering anyone and I have come up empty.

    Here is my specific question:

    How do I calculate the time duration in minutes between two times when the first time is before midnight and the second time is after midnight? For example, how do I calculate the time difference in minutes between 11:00PM and 1:37AM the next the next day?

    Any help is appreciated.

  2. #2
    Registered User
    Join Date
    09-10-2003
    Location
    Detroit, MI
    Posts
    59
    I think you must enter the full date & time for each of these and make sure they are formatted as "Time". Assuming that these two times are in cells A1 & B1, in C1 (formatted as number) enter the formula: "=(B1-A1)*24". This will give you the hours in decimal form (in this case 2.62 hrs). If you want it in minutes then the formula is: "=(B1-A1)*24*60" (this = 157 minutes).
    Neopolitan (Florida Dreaming)

  3. #3
    Ron P
    Guest

    Re: Calculating time difference over midnight!

    "sygazelle" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I have read all of the tips and tricks to try to get an answer without
    > bothering anyone and I have come up empty.
    >
    > Here is my specific question:
    >
    > How do I calculate the time duration in minutes between two times when
    > the first time is before midnight and the second time is after
    > midnight? For example, how do I calculate the time difference in
    > minutes between 11:00PM and 1:37AM the next the next day?
    >
    > Any help is appreciated.


    Assuming first time is in A2 and the later time is in B2 put this in C2
    =B2-A2+(B2<A2) and format the cell as [mm] .


  4. #4
    David McRitchie
    Guest

    Re: Calculating time difference over midnight!

    See http://www.mvps.org/dmcritchie/excel/datetime.htm

    B2: 23:00
    C2: 1:37
    D2: =C2-B2+(B2>C2)

    Why it works, time is a fraction of a day, the comparison B2>C2
    returns True (1) or False (0), if true 1 day (24 hours) is added.
    ---
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "sygazelle" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I have read all of the tips and tricks to try to get an answer without
    > bothering anyone and I have come up empty.
    >
    > Here is my specific question:
    >
    > How do I calculate the time duration in minutes between two times when
    > the first time is before midnight and the second time is after
    > midnight? For example, how do I calculate the time difference in
    > minutes between 11:00PM and 1:37AM the next the next day?




  5. #5
    Big Rick
    Guest

    RE: Calculating time difference over midnight!

    As I dont get to answer many questions, I thought I would give you my way of
    doing this.
    I simply enter the time as 25:30 for 12.30am or 26:30 for 1.30 am. The cell
    can be formatted to read 12.30am or 1.30am etc and the time difference is
    calculated correctly.
    --
    Big Rick


    "sygazelle" wrote:

    >
    > I have read all of the tips and tricks to try to get an answer without
    > bothering anyone and I have come up empty.
    >
    > Here is my specific question:
    >
    > How do I calculate the time duration in minutes between two times when
    > the first time is before midnight and the second time is after
    > midnight? For example, how do I calculate the time difference in
    > minutes between 11:00PM and 1:37AM the next the next day?
    >
    > Any help is appreciated.
    >
    >
    > --
    > sygazelle
    > ------------------------------------------------------------------------
    > sygazelle's Profile: http://www.excelforum.com/member.php...o&userid=27660
    > View this thread: http://www.excelforum.com/showthread...hreadid=471757
    >
    >


  6. #6
    Registered User
    Join Date
    05-02-2017
    Location
    Liverpool
    MS-Off Ver
    2010
    Posts
    1

    Re: Calculating time difference over midnight!

    Brilliant!! Thanks,

  7. #7
    Registered User
    Join Date
    05-25-2017
    Location
    Savannah, US
    MS-Off Ver
    2003
    Posts
    1

    Re: Calculating time difference over midnight!

    =B2-A2+(B2<A2) worked for me!

  8. #8
    Registered User
    Join Date
    01-03-2015
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Calculating time difference over midnight!

    Even easier to just add the 1 anyway:

    =B2-A2+1

    as '+1' adds a full day, 24hrs later, it tends to be exactly the same time again (give or take leap seconds, etc), whether or not passing midnight comes into it

    BUT, if you need the answer to be a DURATION rather than a TIME OF DAY, this won't work though (but neither would the "=B2-A2+(B2<A2)" method.

    If you need a negative duration, you can just set the cell to show 'Number' rather than time, as -hh:mm won't work
    or you can use =ABS() to make a negative into a positive, and keep the value shown in hh:mm format (BUT then remember to subtract it rather than add it in any further calculations)

  9. #9
    Registered User
    Join Date
    02-10-2020
    Location
    Alberta, Canada
    MS-Off Ver
    365
    Posts
    50

    Re: Calculating time difference over midnight!

    Here is how I do it to get over the midnight as well as being able to add a meal break.
    Attachment 662047

    Cell "G" is calculating the hours: =(D2-C2)+(B2-A2)+(D2<A2)
    Cell "E" is converting the calculation in "G" to a decimal number: =G2*24
    Format cells, A,B,C,D & G to time format: hh:mm.
    Convert cell E to number format with 2 decimal places.

  10. #10
    Registered User
    Join Date
    10-27-2020
    Location
    Southampton, England
    MS-Off Ver
    Version 2009 (Build 13231.20390 Click-to-run)
    Posts
    1

    Cool Re: Calculating time difference over midnight!

    Having just come across the need to solve this problem (I was calculating durations which sometimes go over midnight), I solved it by putting the following formula in the duration column of my table:

    I'm using a 24 hour time format.

    =IF($A2 > $B2, ($B2 - $A2) + 24, $B2 - $A2)

    What this is doing is as follows:

    Check: If the time in A2 is greater than B2:

    If true: Perform the difference calculation (B2 minus A2) but add 24 to that result to guarantee a positive number.

    If false: Perform the difference calculation (B2 minus A2).

    I use dollar signs before the column names to make sure the calculation stays locked to that column if I need to move it etc.

    There is 1 flaw with this in that it will only handle periods of up to 24 hours, but for anything beyond that I would just use datetimes anyway.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Calculating time difference over midnight!

    Thanks for the input guys, always appreciated
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  12. #12
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Calculating time difference over midnight!

    If this is still open, =MOD(end_time-start_time,1), but that raises the question whether start_time = end_time should be no elapsed time or exactly 24 hours.

    Always best to use DATE and time even if it needs to be formatted only as time.
    Last edited by hrlngrv; 10-28-2020 at 02:08 AM.

  13. #13
    Registered User
    Join Date
    05-27-2022
    Location
    SE connecticut
    MS-Off Ver
    office 365
    Posts
    1

    Re: Calculating time difference over midnight!

    I am using a Excel 365 on a MacBook air and need to be able to calculate elapsed time that goes past midnight. I have tried all of the solutions above and can't get them to work. I think my problem is with formatting. No matter what time or customer format I use, hh:mm being the most logical but 13:30 with no AM or PM when I enter the time 23:00 will get changed to 11:00PM and 25:00 gets converted to 1:00AM. Any help would be greatly appreciated.

  14. #14
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Calculating time difference over midnight!

    When you enter 25:00, you're effectively entering a date as well as a time. Excel stores the numeric value 1.041666667 (1 + 1/24), where the part to the left of the decimal point is days, and the part to the right is time. If so, you should be able to use a simple formula like =end-start, which for your example would be 1+1/24 - 11/24 = 2/24 or 02:00:00.

    Also, if I give a cell the number format [hh]:mm, then I enter 25:00, the formula bar displays 1/1/1900 1:00:00 AM. The formula bar does that because that's my system short date+time format. To be clear, what's displayed in a cell in the worksheet may differ from what's displayed in the formula bar, and for dates and times neither matches the numeric value.

    Have you formatted all cells with number format [hh]:mm?

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Calculating time difference over midnight!

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed 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