# Calculating time difference over midnight!

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.  Register To Reply

2. 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).  Register To Reply

3. ## 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] .  Register To Reply

4. ## 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?  Register To Reply

5. ## 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
>
>  Register To Reply

6. ## Re: Calculating time difference over midnight!

Brilliant!! Thanks,  Register To Reply

7. ## Re: Calculating time difference over midnight!

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

8. ## 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)  Register To Reply

9. ## 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.  Register To Reply

10. ## 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.  Register To Reply

11. ## Re: Calculating time difference over midnight!

Thanks for the input guys, always appreciated   Register To Reply

12. ## 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.  Register To Reply