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:
=IF(K2="","D",IF(L2="","A",IF(K2<L2,"R","G")))
To give some more information for clarity, Column L is conditionally formatted from Column M. Column M is conditionally formatted from the following rules:
- D = White
- A = Amber/Orange
- R = Red
- G = Green
What I am trying to achieve is keeping the formula working as it is but I want to add something to it that instructs the sheet to turn the completion status (Column M) Red if no date of completion (Column L) is entered and the timescale for completion (Column K) date has lapsed over the current date (Cell N1).
Thanks in advance! I hope I have explained everything reasonably well!
Aaron
- Does anyone have any light to shed on this?
Try:
=IF(K2="","D",IF(L2="",IF(K2<NOW(),"R","A"),IF(K2<L2,"R","G")))
Hi Ismael
Thanks for your reply but this hasn't produced the outcome I was looking for but it has stemmed an idea.
Thanks!
Please share the outcome you were expecting. I used the NOW function to extract current date but that can easily be pointed to a cell.
Your criteria seems to have conflicting demands. All dates will eventually laspe when compared to the current date. You'll need to prioritze the criteria. Attahced is a file which I believe meets the criteria assuming "M" will always be green if "L" date is before "K" date regardless of today's date. If this does not meet your needs please indicate in the sheet which "M" states aren't correct.
- Hi Ismael,
Your formula seems to work the same as my current. Which is perfect apart from what I want to add, as follows:
If the date in Column L (Completion) Lapse over Column K (Timescale) It should go Red.It does. If L is past K it resutls in R.
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()))
