Forum Discussion
Calculate difference between two times in Excel for Mac
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
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://excelribbon.tips.net/T006239_Displaying_Negative_Times.html
Please see file attached
6 Replies
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) AvidanYes, that works. But, IMHO, with formula is more safe.
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)
Thank you, Sergei!
Any suggestions as to how to deal with the ones that arrived earlier than scheduled? The other ones worked perfectly.
