+ Reply to Thread
Results 1 to 8 of 8

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" <sygazelle.1w4lma_1128006307.1878@excelforum-nospam.com> wrote
    in message news:sygazelle.1w4lma_1128006307.1878@excelforum-nospam.com...
    >
    > 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" <sygazelle.1w4lma_1128006307.1878@excelforum-nospam.com> wrote in message
    news:sygazelle.1w4lma_1128006307.1878@excelforum-nospam.com...
    >
    > 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
    49

    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)

+ 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