Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

3 Answers 3

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

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
add comment

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 –  kbsou 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 –  kbsou 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? –  kbsou Feb 19 '13 at 17:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.