Forum Discussion
Formatting cell to change font color based on a broken rule
I'll be as specific as possible. I want to format a cell so that IF say cell A1 has a value less than 40:00, the font color is red. 40:00 or higher is black. Also the value displayed in cell A1 comes from what is shown in a different cell.
5 Replies
Yes. Use conditional formatting rules.
the first option is based on value, the sub-menu the second option is then <
it then prompts with what value and you can try 40:00 but you may need to convert that to a value. is that 40 hours or 40 mins? I would use =40/24 for hours and =40/24/60 for mins.
Select the cell or cells with such time values.
Click the arrow in the lower right corner of the Number group of the Home tab of the ribbon, or select More Formats... from the Number Format drop down in that group.
Select Custom in the list of categories.
Enter the following custom format in the Type box:
[Red][<1.66666666666666][hh]:mm;[hh]:mm
Click OK.
- Thank you . That works just as I wanted.
What is the formula for 24:00 in the same scenario?I used 1.66666666666 because 40 hours = 40/24 day = 1.6666666666666
24 hours = 1 day, so use 1 instead of 1.66666666666
