MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

=============