negative time expressed in -hh:mm:ss
Posted by Kelly on September 28, 2001 6:18 AM
I am doing schedule adherence. I need to be able to take the hh:mm:ss value in cell B1 (scheduled departure time) and subtract the hh:mm:ss value in cell A1 (actual departure time) and place the answer in cell C1 as value mm:ss, where cell C1 may have a negative value -mm:ss. Two examples follow:
A B C
1 22:35:14 22:40:00 04:46
2 22:42:37 22:40:00 -02:37
Seems simple, please help....
Posted by Juan Pablo on September 28, 2001 7:26 AM
Try this one, it results as text, but it works
=IF(B1-A1<0,"-"&TEXT(ABS(B1-A1),"[mm]:ss"),B1-A1)
And format C as [mm]:ss
Juan Pablo
Posted by Aladin Akyurek on September 28, 2001 7:34 AM
Or...
Switch to the 1904 date system to allow for negative times.
Aladin
===========
Posted by Juan Pablo on September 28, 2001 7:39 AM
Ok, that's better.... (NT)
Posted by Barrie Davidson on September 28, 2001 7:42 AM
Aladin, how did you know that ???(nt)
Posted by Aladin Akyurek on September 28, 2001 7:55 AM
Accidental...
I have a spreadsheet with columns: A,B,C(Fields)
I would like to populate D for every record with the value of C only if either A=B or AlikeB
All help and time is much appreciated.
Manu
Posted by Kelly on September 28, 2001 8:15 AM
Thanks! This seems to work very nicely for my application even though the result is text.
Posted by Aladin Akyurek on September 28, 2001 8:51 AM
Accidental...
discovery: On my Mac Excel does allow for negative times, but Excel on Virtual PC (Windows) [also on the same Mac] did not. Excel on Mac has the 1904 date system as default.
=============
