Forum Discussion
Formulas and Functions
Please Help - I'm no Excel expert!!
I am trying to improve a log of audit actions, more specifically their timescales for completion.
This is the formula I currently have in Cell M2:
=I...
Hi Aaron,
Thank you for the sample. Thus you need 4 rules, one for each color, and sequence of rules matter. The logic could be
First we check if K is empty and white the cell if so
=$K2=""
Second we check if L is not empty and less than K, green that
=($L2<=$K2)*($L2>1)
Third we check for L is not empty if K less than today OR L more than K or today, red then
=OR(($K2>1)*($K2>=TODAY()),OR($L2>$K2,$L2>=TODAY()))*($L2<>"")
Last rule we check if L is empty or K less than today, orange it
=OR($L2="",($K2>1)*($K2<TODAY()))
Thank you for that Sergei
How will these rules translate into one formula?
Am I on the money with:
=$K2="",=($L2<=$K2)*($L2>1),=OR(($K2>1)*($K2>=TODAY()),OR($L2>$K2,$L2>=TODAY()))*($L2<>""),=OR($L2="",($K2>1)*($K2<TODAY()))
How will these rules translate into one formula?
Am I on the money with:
=$K2="",=($L2<=$K2)*($L2>1),=OR(($K2>1)*($K2>=TODAY()),OR($L2>$K2,$L2>=TODAY()))*($L2<>""),=OR($L2="",($K2>1)*($K2<TODAY()))