# Calculating time in Excel between AM and PM

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?

-

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

-
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)

-
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