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...
So the sample is attached.
Hopefully a better explanation of what I am trying to achieve:
So..
- The formula related to Cells in Column M
- If no date is entered in Column K enter "D"
- If a date is entered in Column K and it is less than the current date enter "A", if it is greater than the current date enter "R"
- If a date is entered in Column L which is greater than the current date or the date entered in Column K enter "R"
- If the date in Column L is less than Column K and the current date enter "G"
Thanks in advance.
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()))Aaron, do you mean to use exactly the same formula for all rules?
