Time difference function
Not sure what you're trying to do here.
The difference shows up as #####
Those show up when you try to subtract formatted time values and the result
is a negative value. Using the default date setting Excel won't display
negative dates/times and it gives you those lovely hash marks.
03:00 = 0.002083333
02:04 = 0.086111111
The 02:04 is incorrect. 2 minutes 4 seconds is 0.00143518518518519
0.0861111111111111 is 2 hours 4 minutes
When you enter a time you must enter it with the hour.
0:3:00
0:2:04
You can format the cell to not display the hour so that it appears as:
3:00
2:04
--
Biff
Microsoft Excel MVP
"Evan" wrote in message
...
I'm having problems finding the difference between to columns of time
formatted into mm:ss. The difference shows up as ##### So, I converted
each
column of mm:ss into numbers with this formula:
=TIMEVALUE(TEXT(I3,"hh:mm"))
such that 03:00 = 0.002083333, 02:04 = 0.086111111 the difference =
0.088194444 using format custom mm:ss to reconvert back to time is
07:00. Now I'm stuck just not sure which function I should use to
correctly
reconvert the difference back into "mm:ss" Any help is greatly
appreciated.
Thanks EVan
|