Conditional formatting

Question

How to get excel to dynamically format cells based on their content. The content is formatted as time (hh:mm:ss) For example I want all cells with a value of less than 6 hours to be red. None of the options I can find on the internet work

Answer 1

Thanks but that only works for one cell.

How do I get it to check either a whole column or a whole row, and I want to also add a different colour if it is <6 >12, and yet another if > 12

Thanks for your help. The colons instead of the commas worked.

Answer 2

Could you please try to change the " , " to " ; " ? , the formula will then becomes:

=$A2<=TIME(6;0;0)

Thanks

Answer 3

Tells me there is a problem with that formula.

I changed A2 to the cell I was trying to format

Answer 4

I've tried creating an excel file for you and below is the link. Kindly download the file and see if it works as what you need:

https://drive.google.com/file/d/1JtQ_Ky84VyIEv9...

What I did was:

1. Under Conditional formatting, i clicked on the new rule then,
2. click on the "Use a formula to determine which cells to format"
3. Under the "Format values where this formula is true:" I use this formula:

=$A2<=TIME(6,0,0)

**I converted the value of the cell in a2 to time, since it is read by excel as numbers under conditional formatting.

4. I clicked on Format, then on the Fill tab I chose the red color.
5. Then click on Ok.

If you want to edit it and apply it to all other cells, you may click on the Conditional Formatting again, and look for Manage Rules, under "Applies to:" you may select in which cell you would wanted to apply it.

Hope this helps.

Thanks