Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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]


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


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

share|improve this answer
Thanks, that worked. – cooleyd5 Feb 19 '13 at 17:27
@cooleyd5, no problem - if it works for you can you accept my answer, thanks – barry houdini Feb 19 '13 at 18:48
Hi Barry. This worked except the answer also shows either AM or PM. Is there a way for the answer to only show the time without the AM or PM? Thanks. – cooleyd5 Mar 21 '13 at 11:30
That should just be a case of changing the format to suit. Right-click cell with formula and use Format Cells > Number > Custom and type required format in box - h:mm - > OK – barry houdini Mar 21 '13 at 13:24

If you just use PM and AM, Excel assumes that is the same day. Since PM comes after AM the result of B1 - A1 will be a negative number. You can use =Max(A1, B1) - Min(A1, B1) to always get the ellapsed time.

You can apply [m] as custom number format to always display the number of minutes (instead of hours, once the number of minutes is greater than 60)

share|improve this answer
This might help – kb_sou Feb 19 '13 at 14:28
I tried it, but I get a lot of pound (#) symbols in the cell. – cooleyd5 Feb 19 '13 at 16:39
Try to resize the column width – kb_sou Feb 19 '13 at 17:10
Tried that too and there were too many symbols. – cooleyd5 Feb 19 '13 at 17:15
What is the result if you set the format to Date? – kb_sou Feb 19 '13 at 17:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .