conditional formatting with time values

[Guest]

Hi, thanks for any help with this if you can.
I have a spreadsheet where I have calculated the time it takes to make a
trip accordong to our report, and one showing the time it actually takes.
Both values are in time format as hours: and minutes 02:43 ( 2 hours 43
minutes ).
My goal was to compare the 2 times and show if the report was alloting too
much time, or too little time. I tried simply subtracting one from the
other, but I found that while it worked when the initial value was higher (
large time value minus Smaller time value ) it worked fine, but not the other
way around. I got around this by using an If Statement, testing for which
was the larger value and then subtracted accordingly. The values seem
accurate, but I want to put in conditional formatting so that when the report
is allotting too much time the time value is RED. And when the report is not
Alloting enough time The value is Yellow.
Nothing I do in conditional formatting has allowed me to do this.
Help?

Todd

 

[Ian]

My goal was to compare the 2 times and show if the report was alloting too
much time, or too little time. I tried simply subtracting one from the
other, but I found that while it worked when the initial value was higher
(
large time value minus Smaller time value ) it worked fine, but not the
other
way around.

accurate, but I want to put in conditional formatting so that when the
report
is allotting too much time the time value is RED. And when the report is
not
Alloting enough time The value is Yellow.

If you go to Tools|Options|Calculation and check 1904 date system, your -ve
times will display OK. You can then use conditional formatting to test for

 

[Bob Phillips]

Depends upon what way you want to subtract, is 12:00-09:00 3 hours or 21?

So either

=ABS(B20-A20)

or

=MOD(B20-A20,1)