Feb 21 2018
01:37 AM
- last edited on
Jul 25 2018
11:07 AM
by
TechCommunityAP
Feb 21 2018
01:37 AM
- last edited on
Jul 25 2018
11:07 AM
by
TechCommunityAP
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 |
Feb 21 2018 01:43 AM
Actually the time is number. You may apply the rule like
=$B2>TIME(9,2,0)
to highlight the cells
Jul 13 2023 06:52 AM
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.
Jul 13 2023 06:53 AM
Jul 13 2023 08:29 AM
@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
Jul 13 2023 08:53 AM
Jul 13 2023 08:54 AM
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)
Jul 13 2023 09:00 AM
Jul 13 2023 11:59 AM
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.
Jul 13 2023 12:19 PM
Jul 13 2023 12:34 PM
On your screenshot color for two cells with value more than 59s is changed. Same is here:
Which result do you expect?
Oct 27 2023 07:57 PM - edited Oct 27 2023 07:59 PM
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?
Oct 29 2023 08:53 PM
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:
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.
Oct 30 2023 07:45 AM - edited Oct 30 2023 08:40 AM
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.