Conditional formatting with formulas

[mattdennis1402]

Hi Team

Im having some issues figuring out you to make the below work

Employee works a 38 hour week and we need a certain amount of the hours to be "billable".

If employee works 38 hour week:
Green - > 20 hours
Yellow - 17.5 - 19.99 hours
Red - < 17.50 hours

I would like to use the same methodology for employees that work less hours. for example someone who works 16 hours. hope this makes sense

Can anyone assist with this? example file attached

[Gregb11]

1. What's wrong with what you are doing currently?
2. If someone works 16 hours (and any hours not equal to 38), what are the thresholds for the colors?

[mattdennis1402]

essentially i need it to be a weighted target

if 38 worked hours needs to be above 20 to be green, then 16 worked hours they need to be about ~ 12 to be green

[Gregb11]

How do you get ~12? 20 to 38 is about 52.3%. That same percentage for 16 would be 8.42 hours.

[mattdennis1402]

sorry your correct. i used the same math on 24 hours not 16 like i stated

[Gregb11]

Maybe this:
Formula for Green: =C14/C12>20/38
Formula for yellow: =C14/C12>=17.5/38
Formula for Red: =C14<>""
For all above, the "Applies to" should be:
=$C$14:$N$14
Also, they should be listed in the order shown above. See attached.

[mattdennis1402]

Thank you so much, that worked perfectly.

much appreciated