Displaying negative time in excel

  • Thread starter Six Sigma Blackbelt
  • Start date
S

Six Sigma Blackbelt

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.
 
B

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
 
B

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
 
G

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
Click to expand...
 
Top