Forum Discussion
use conditional formatting with time
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 ...
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.
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.
SergeiBaklan 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
- 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)
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.
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)
- It's not working on my end 😞 Just tried it