Calculate difference between two times in Excel for Mac

[Hugh Finerty]

I had to separate the times from the date columns that were formatted as date and time. After I separated out the times, I applied the simple formula =N2-O2 to calculate the time differential. But, it is returning an infinite number of #######.  I uploaded the file for help. I am trying to find the time differential, minutes and seconds, between columns N and O. Any help would be appreciated. Thanks! Hugh

[#2]

Hi Hugh,

Better for the difference of time to use

=H2-G2

and apply custom format

[hh]:mm:ss

You only will have kind of formatting error if the time is negative (received time is earlier than start time)

[#3]

Thank you, Sergei! 

[#4]

Any suggestions as to how to deal with the ones that arrived earlier than scheduled? The other ones worked perfectly.

[#5 · Accepted solution]

Hi Hugh,

If negative elapsed time is the case you may use text to show it

=IF(H2-G2 <0, "-" & TEXT(G2-H2, "[HH].mm.ss"), H2-G2)

more details at

https://support.microsoft.com/en-us/help/182247/negative-date-and-time-value-are-displayed-as-pound-...

https://excelribbon.tips.net/T006239_Displaying_Negative_Times.html

Please see file attached

[#6]

As far as I recall one can present Negative time in Excel for Mac. 
Goto Tools > Options > Calculation > mark 1904 date system.
This should enable the presentaion of negative times.
-----------------------
Michael (Micky) Avidan

[#7]

Yes, that works. But, IMHO, with formula is more safe.