use conditional formatting with time

[M.Yasir Fakhr]

how to use conditional formatting with time,

If cell B is greater than 09:02:00 AM it should be highlighted in yellow, 

waiting best response

Date	Time	Remarks
1-Feb-18	8:50 AM	C/In
1-Feb-18	5:59 PM	C/In
2-Feb-18	9:21 AM	C/In
2-Feb-18	6:02 PM	C/Out
3-Feb-18	8:58 AM	C/In
3-Feb-18	5:50 PM	C/Out
6-Feb-18	9:18 AM	C/In
6-Feb-18	6:06 PM	C/Out
7-Feb-18	12:56 PM	C/In
7-Feb-18	6:05 PM	C/Out
8-Feb-18	9:22 AM	C/In
8-Feb-18	6:03 PM	C/Out
10-Feb-18	9:28 AM	C/In
10-Feb-18	6:03 PM	C/Out
13-Feb-18	9:20 AM	C/In
13-Feb-18	6:02 PM	C/Out
14-Feb-18	1:20 PM	C/In
14-Feb-18	6:10 PM	C/Out
15-Feb-18	9:11 AM	C/In
15-Feb-18	6:01 PM	C/Out
16-Feb-18	8:48 AM	C/In
16-Feb-18	5:59 PM	C/Out

[#2]

Actually the time is number. You may apply the rule like

=$B2>TIME(9,2,0)

to highlight the cells

[#3]

Thank you 

Mr. Sergei Baklan

[#4]

@M.Yasir Fakhr 

I have a problem regarding to Using Conditional Formatting Times in Excel.

I need a range of times formatted as hh:mm:ss to be red if the time is ABOVE 00:01:00 and green if the time is BELOW 00
:00:59

For example I have:
00:00:24
00:00:37
00:00:17
00:01:27
00:00:28
00:00:18
00:01:11
00:00:07

Two of these I want to turn red 00:01:11 and 00:01:27 but the rest should turn green.

[#5]

I have a problem regarding to Using Conditional Formatting Times in Excel.

I need a range of times formatted as hh:mm:ss to be red if the time is ABOVE 00:01:00 and green if the time is BELOW 00
:00:59

For example I have:
00:00:24
00:00:37
00:00:17
00:01:27
00:00:28
00:00:18
00:01:11
00:00:07

Two of these I want to turn red 00:01:11 and 00:01:27 but the rest should turn green.

[#6]

@ShainaChoate 

That could be like

[#7]

@Sergei Baklan Thank you for the quick response. I am an excel newbie and having a hard time understanding how you got that to work, what is this formula actually saying? The 59/24/60/60 part

[#8]

It's not working on my end :( Just tried it

[#9]

@ShainaChoate 

In Excel formatted date and time are only user friendly notation. Actually datetime is decimal number. Days are integer part, i.e. 1 day = 1. Dates starts from Jan 01, 1900. This date as number is 1. Today, Jun 13, 2023 as a number is 45120.

Time is decimal part. In one day are 24 hours, each has 60 minutes and each minute has 60 seconds.

Thus 59 seconds (00:00:59) is equal to 59*(1/24/60/60)

[#10]

I am using a custom format for these cells of hh:mm:ss , does that matter? I want cells that reflect HIGHER than 00:01:00 (greater than a minute) to be red and cells that are EQUAL TO OR LOWER than 00:00:59 to be Green (less than a minute)

[#11]

@ShainaChoate 

Format doesn't matter, it's only the way to show the number, not to change it.

Here

is exactly the same number to which are applied different formats.

[#12]

@Sergei Baklan 

I've tried your method and as you can see it just turned them all the same color?! 

[#13]

@ShainaChoate 

On your screenshot color for two cells with value more than 59s is changed. Same is here:

Which result do you expect?

[#14]

@Sergei Baklan 

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?

[#15]

@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.

[#16]

@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.