Displaying negative time in excel

[Guest]

I have a worksheet that displays appt. time & actual time in, some of my
actual time in data is greater than the appt. time ex: 13:15 appt time,
Actual time in 13:18, the cell should show :03 in red? but instead it gives
me a #VALUE!. How can I display this time, and show it as a negative time.

 

[David Biddulph]

1904 date system.

 

[Bernie Deitrick]

6,

You need to use the 1904 date system to allow negative times Tools / Options / Calc tab Check
"1904 Date system" Note that this will throuw off any entered dates on you worksheet unless you
subtract 1492 from all of your date constants....

HTH,
Bernie
MS Excel MVP

 

[Biff]

Use conditional formatting.

A2 = 13:15
B2 = 13:18

C2 = formula:

=ABS(A2-B2)

C2 conditional formatting:
Formula Is: =(A2-B2)*24<0
Set the font color to red
OK out

Biff

 

[Guest]

Try this:
=MAX(A2:B2)-MIN(A2:B2)

 

[Gord Dibben]

Bernie

Make that 1462


Gord

6,

You need to use the 1904 date system to allow negative times Tools / Options / Calc tab Check
"1904 Date system" Note that this will throuw off any entered dates on you worksheet unless you
subtract 1492 from all of your date constants....

HTH,
Bernie
MS Excel MVP