Need to color code by time with conditional formatting

needhelp.xlsHi.

I am trying to use conditional formatting to color code a column of times.

The format is minutes:seconds

If the time is less than 3:30 it needs to be formatted with a green fill
If the time is 3:30-5:00 it needs to be formatted with a yellow fill
If the time is above 5:00 it needs to be formatted with a red fill

Basically the goal of the business is to have the time under 3:30 every day and my job is to rank and recap this performance.

So far I have tried going to formatting and choosing the mm:ss option
Then I highlight the entire column
I go to conditional formatting and create a new rule
Format only cells that contain cell value less than or equal to =time(3,30) and it says enter a valid formula
So I tried =time(3,30,0) and it let me do it
format fill green

Then when I run the rule nothing happens.

Please help!

Aardigspook

That worked way better than what I was doing.

Almost perfectly!

The only problem I am running into is that it is coding 3:01 as yellow when it should be green.
I'll include a clip of what is going on and if you have any more advice I would very much appreciate it!

Thanks!

test.png

Ok, not entirely sure why it's doing that, but I do have a way to fix it.

Instead of formatting your cells as mm:ss, format them as [h]:mm. Then change the conditional formatting to remove the quotation marks, so ="3.5/24" becomes =3.5/24 etc.

The [h] is used instead of h without [] so that you can total to more than 24, which you'll obviously need for minutes, if you're totalling in rows 8 and 16 (though the figures in there aren't totals at the moment).

You may also want to remove the conditional formatting from blank cells (D9), either by deleting it from those cells or by adding another conditional format with Cell Value ="" with no format set.

Here's your file back with the changes done - note that I've saved it as an .xlsx file, because .xls file type only supports 3 conditional formats, so the 'blank' format wouldn't work in that older file type.
needhelp _ CF mins_secs for ibooklover123.xlsx

Hope that now works for you.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Hey Thanks!!!!

That did it - though for some reason to get it to change the formatting i had to individually double click each cell. No big deal though - i recorded the whole thing as a macro so I can just run it every day.

One step closer to automating my entire morning routine so I can wake up 10 minutes later!


Thanks again

=)

You're welcome - enjoy your extra time in bed

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.