Displaying negative time in excel
 

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.

Displaying negative time in excel
 

1904 date system.
--
David Biddulph

"Six Sigma Blackbelt" wrote in
message ...
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.

Displaying negative time in excel
 

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


"Six Sigma Blackbelt" wrote in message
...
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.

Displaying negative time in excel
 

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

"Six Sigma Blackbelt" wrote in
message ...
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.

Displaying negative time in excel
 

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


"Six Sigma Blackbelt" wrote:

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.

Displaying negative time in excel
 

Bernie

Make that 1462


Gord

On Fri, 10 Nov 2006 12:38:52 -0500, "Bernie Deitrick" <deitbe @ consumer dot
org wrote:

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


"Six Sigma Blackbelt" wrote in message
...
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.