Excel Time Fields and Formulas
Posted by Ryan Currie on June 01, 2001 6:51 AM
Hey all,
I have an Excel Sheet that compares the arrival and departure time of emergency services. I use a column for "Departure Time" (Column B) and a column for "Arrival Time" (Column C). Both these fields are in hh:mm Time format. I then subtract these fields using SUM(C1,-B1) to get the "Elapsed Time" (Column D, also a Time field).
I then compare these times against a set "Goal" time. I created another column "Factor" (Column E, Time format) and placed in it the goal time (for example: 0:21). I then subtract each "Factor" row from each "Elapsed Time" row to get the amount of time the service was late or early, and store this value in a "Result" column (Column F) using the formula SUM(E1,-D1).
My problem is, I'd like to use Conditional Formatting to color the "Result" field values according to the result. If the time is positive (ie: the service arrived early), I'd like the field to be green. If the time is negative I'd like the field to be red. If its on time (ie: Factor-Elapsed = 0:00), I'd like it to be yellow.
When I conditional format, I can't get it to work properly when the Result is 0:00 (the Elapsed Time and Factor are equal). From playing around, I find that the "real" formula result is 2.4E-17 instead of 0:00. it works fine if the Result is greater than 0:00 or less than 0:00.
Can anyone point me in the right direction here? Sorry for the length.
Thank you muchly,
- Ryan