Subtracting Cells in Time Format

Posted by Gerald Marshall on November 11, 2001 2:45 AM

In Cell A1 I have time taken 2:30, In Cell B1 I have a time allowed 1:30.
In Cell C1 I have the formula =B1-A1. I want to end up with the figure -1:00 in red font.

This is a very simplistic example but I can not get the result I want.
I would appreciate any help.

Thank you

Gerald Marshall

Posted by Jack on November 11, 2001 2:57 AM

Use Conditional format
Select range to conditional fromat,
From Tabs select Format
Conditional Format
Set to less than and next box put 0
Click formatt and change Fromt to red

That should do it OK
Good luck

PS your need to format the number to 2 places

Posted by Juan Pablo on November 11, 2001 9:50 AM

To have negative times, you have to set the time system to 1904, then, you can custom format, something like:

hh:mm;[Red]-hh:mm

That should get you the results that you expect.

Juan Pablo

Posted by Gerald Marshall on November 11, 2001 10:48 AM

Re: Setting Time System

Thanks for your help Juan, however could you explain what you mean by setting the time system to 1904. This is new territory for me.

Thanks

Gerald

Posted by Richard S on November 11, 2001 9:07 PM

Re: Setting Time System

Tools Menu, Options tab and check the 1904 date system at the bottom left. Care, this may effect the way existing spreadsheets treat times.

Richard