Excel – Calculating time in Excel between AM and PM

date timemicrosoft excelworksheet-function

I have an Excel spreadsheet that calculates the difference between two times. The formula that I'm using, for example, is as follows:

Cell A1 12:00 PM
Cell B1 12:30 PM
Cell C1 =minute(B1-A1) and the result is 30 which is what I'm looking for, but when I try:


Cell A1 11:55 PM
Cell B1 12:05 AM what formula do I use to get the result of 10?

I get an error or wrong calculation. Can someone help me with this so that I'll get the correct result of 10?

Also, if the time is, let's say, 71 minutes, how can I get a result of 1 hour and 11 minutes if possible?

Best Answer

You can get the correct difference in minutes using MOD function, e.g. to get 10 for a 10 minute difference and 71 for one hour 11 minutes you can use this formula [revised]

=MOD(B1-A1,1)*1440

format result cell as general

Use of MOD function ensures that will work even when the time gap passes midnight. If you leave out the *1440, i.e. use just

=MOD(B1-A1,1)

and format as h:mm you will get 0:10 or 1:11

Related Question