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.


--
sygazelle
------------------------------------------------------------------------
sygazelle's Profile: http://www.excelforum.com/member.php...o&userid=27660
View this thread: http://www.excelforum.com/showthread...hreadid=471757

"sygazelle" wrote
in message ...

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] .

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

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

Why it works, time is a fraction of a day, the comparison B2C2
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" wrote in message
...

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?

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


------------------------------------------------------------------------
neopolitan's Profile: http://www.excelforum.com/member.php...nfo&userid=611
View this thread: http://www.excelforum.com/showthread...hreadid=471757

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