Home |
Search |
Today's 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. -- sygazelle ------------------------------------------------------------------------ sygazelle's Profile: http://www.excelforum.com/member.php...o&userid=27660 View this thread: http://www.excelforum.com/showthread...hreadid=471757 |
#2
|
|||
|
|||
"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] . |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting from time format to decimal and figuring the difference | Excel Discussion (Misc queries) | |||
Time zone Time difference | Excel Worksheet Functions | |||
need help: time difference between two ceels in a column | New Users to Excel | |||
How do you find the difference between two time values when one i. | Excel Discussion (Misc queries) | |||
What is the formula for getting time difference e.g. ("4 hrs 15 m. | Charts and Charting in Excel |