use conditional formatting with time

SnowMan55

Thanks that worked.  I wasn't using 86400 as the multipying factor so getting erroneous results. This is the reason why I shifted to Text. 

For milliseconds, yes, it should be 000 format, however my stopwatch has only 2 digits for milliseconds. 

The advantage of using Conditional formatting was you could 'color' the cell background ("Fill") as well, so the font color works great with contrasting background. However, since I wasn't able to compute negative time, it wasn't working. Now, I tried with your suggestion and it works great.

On "over time" and "under time", I was trying to track my timinings to see if I added or lost time (compared to my previous attempt).

Thanks again your response and help. 

AkashGeek 

Note that the formatting of milliseconds requires three digits to the right of the decimal point, not two.

See the attached workbook.

Excel's failure to format negative time differences/durations as expected is annoying. My recommendations, in combination, are:

• Do not use the 1904 date system to resolve the issue with negative times.
• Do not convert the values to text. Keep the calculated differences as a number … in seconds, just not Excel time values.
• Less importantly, do not use conditional formatting. Instead, use a custom format with conditions. (I am not sure what you mean by "Over Time" and "under time", but if your concept does not match the custom format I used, the necessary modifications should be obvious.)
• Do not use "Green", as I did in the workbook; its legibility on a white background is poor. Try "color 10" instead.

For a description of custom formats with conditions, see Custom Excel number format  But the article A comprehensive guide to Number Formats in Excel shows more color choices.

I hope that helps.

SergeiBaklan 

Hi Sergei

I am trying to use Conditional formating to identify difference in time (measured in seconds and milliseconds; "SS.00" format). Over Time in Red and under time in Green. I am using the following formula for computing the difference :

Text(A6-A8,"SS.00")

I enabled 1904 number system to see negative or positive values, however when I apply conditional formating, it does not read the difference as negative. 
Can you help me here?