Forum Discussion

Steve_Barone's avatar
Steve_Barone
Copper Contributor
Sep 18, 2020

Time Conditional Formatting Help

Hi Excel folks,

 

I'm stuck on how to get a conditional formatting for time to work. I've tried MANY different iterations and it's not working. One of them seemed to highlight part of what I wanted but it still wasn't correct. Once I changed it just slightly, it completely broke and highlighted everything. Any help would be appreciated!

 

I am trying to write a conditional formatting formula that says anything in column M that is greater than 15 minutes, (0,15,1) should be highlighted. I've tried using the "Format all cells based on their values" and set one for <TIME(0,15,0) and one for >TIME(0,15,1) but that doesn't do anything at all. Same thing with ="TIME(0,15,1)" and ="TIME(23,59,59)" for min/max values

 

I've also tried =TEXT($B$1,"hh:mm:ss")>"00:15:00" but that highlighted everything in the entire column.

 

I've tried using the one below but that too doesn't work, it highlights random things but not what I want.

=(M2+0 > TIME(0,15,0))

 

I tried formatting column M as time hh:mm:ss in cell format, with no real difference. I then tried formatting it as text, and same, no difference. I don't mind if I get a negative value, represented by #VALUE! since I can filter those out.

 

Colum K                        Column L      Column M

3:00:00 PM4:01:43 PM1:01:43
1:00:00 PM1:57:49 PM0:57:49
10:00:00 AM9:02:00 AM#VALUE!
10:00:00 AM11:02:09 AM1:02:09
9:00:00 AM10:30:00 AM1:30:00
9:30:00 AM9:00:00 AM#VALUE!
9:00:00 AM9:32:00 AM0:32:00
1:00:00 PM3:00:00 PM2:00:00
2:00:00 PM1:57:34 PM#VALUE!
10:30:00 AM12:07:00 PM1:37:00
10:00:00 AM9:04:53 AM#VALUE!
11:00:00 AM11:09:14 AM0:09:14
8:00:00 AM8:13:12 AM0:13:12
3:00:00 PM4:00:00 PM1:00:00
1:00:00 PM1:06:05 PM0:06:05
10:00:00 AM10:15:00 AM0:15:00
8:00:00 AM8:22:04 AM0:22:04
8:30:00 AM8:33:00 AM0:03:00
1:00:00 PM12:58:14 PM#VALUE!
2:00:00 PM3:00:32 PM1:00:32
2:15:00 PM #VALUE!
11:30:00 AM11:35:00 AM0:05:00
10:30:00 AM10:10:00 AM#VALUE!
1:00:00 PM1:06:00 PM0:06:00
2:30:00 PM2:54:00 PM0:24:00
2:30:00 PM1:15:00 PM#VALUE!
10:00:00 AM10:16:36 AM0:16:36
11:00:00 AM10:49:00 AM#VALUE!
8:30:00 AM8:55:00 AM0:25:00
1:00:00 PM1:15:00 PM0:15:00
3:25:00 PM4:10:00 PM0:45:00
10:00:00 AM10:50:57 AM0:50:57
3:25:00 PM4:10:00 PM0:45:00
10:00:00 AM10:25:08 AM0:25:08
11:00:00 AM11:25:00 AM0:25:00
excel
Formulas and Functions

6 Replies

    • Steve_Barone's avatar
      Steve_Barone
      Copper Contributor
      Sep 18, 2020
      Thanks, I attached a bit of the data in my other reply, with all sensitivity removed.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 18, 2020

        Steve_Barone 

        The formula in C2 should be =B2-A2, not =TEXT(...).

        And your conditional formatting formula should not refer to M2 in this sample workbook but to C2, of course.

        But it's easier to use a rule of the type "Format only cells that contain".

         

         

    • Steve_Barone's avatar
      Steve_Barone
      Copper Contributor
      Sep 18, 2020

      SergeiBaklan 

       

       

      I attached a sample of my data, I am using Office 2016.

       

      When I tried it, it wouldn't work the way it did for you. Does it matter that I factored in headers? I made it M2 instead of M1. Not sure if my picture that I tried to upload worked, I can't see it again.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 19, 2020

        Steve_Barone 

        Another option compare to what HansVogelaar suggested is keep the range to which conditional formatting is applied as it is, i.e. entire column C. I'm not sure why you convert time difference into the text, but we also could keep it as it is. 

        With that we may apply the rule as

        using absolute references on Start/End columns and relative reference on current row, starting from very first row of the range, i.e. first one.

         

        With that it doesn't matter which information you return into column C, we only format it depends on values in Time and Actual Time columns.

         

        Bottom line - creating formula for the rule always do it for the first cell of the range to which the rule is applied and take care about absolute/relative references to be sure formula correctly works if virtually move it on another cells.

Resources