Feb 14 2018 08:32 AM - edited Feb 15 2018 05:03 AM
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:
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
Feb 15 2018 07:13 AM
Try:
=IF(K2="","D",IF(L2="",IF(K2<NOW(),"R","A"),IF(K2<L2,"R","G")))
Feb 15 2018 07:17 AM
Hi Ismael
Thanks for your reply but this hasn't produced the outcome I was looking for but it has stemmed an idea.
Thanks!
Feb 15 2018 07:21 AM
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.
Feb 15 2018 07:25 AM
Feb 15 2018 07:42 AM
If you want column M to turn red (R) when the K date has lapsed (which it will always do over time) regardless of what's in L, then this should work:
=IF(K2="","D",IF(L2="",IF(K2<NOW(),"R","A"),IF(K2<NOW(),"R",IF(K2<L2,"R","G"))))
Feb 15 2018 07:48 AM
No this hasn't worked.
If the completion time scale lapse over the current date it should go red, if it is completed (Column L less than todays date) It should go green.
I only want Column M to go red if the date specified in Column K or L lapse over the current date.
If there is no date in Column L and the date in Column K has NOT lapsed over the current date then it should be Amber.
If the date in Column L is less than Column K it should go Green.
I apologise if I haven't explained this as best as I could have.
Feb 15 2018 08:41 AM
Hi Aaron,
It will be much better if you provide sample Excel file instead of screenshot - to play with formulas we need to build the model from scratch based on your description. Bit time consuming.
Feb 15 2018 08:44 AM
In the morning I will remove all confidential data and provide a sample for you.
Thanks.
Feb 15 2018 10:46 AM
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.
Feb 16 2018 12:16 AM
So the sample is attached.
Hopefully a better explanation of what I am trying to achieve:
So..
Thanks in advance.
Feb 16 2018 12:58 AM
Feb 16 2018 02:12 AM
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()))
Feb 16 2018 02:16 AM
Feb 16 2018 03:39 AM
Aaron, do you mean to use exactly the same formula for all rules?
Feb 16 2018 03:45 AM
Feb 16 2018 05:22 AM
I missed what if L is empty and K > today() that shall be red.
Combined formula could be
=IF($K2="","D", IF( ($L2>1)*AND($L2<=TODAY(),$L2<=$K2),"G", IF(OR(($L2>1)*OR($L2>TODAY(),$L2>$K2),($K2>TODAY())), "R", "A" ) ))
and in any case you need the letters rules could be as
=IF(as above) = "D"
and so on for each color
Feb 19 2018 12:19 AM
Unfortunately this doesn't work as I hoped either..
As you can see in the sample, Completion status is Red, though the no completion date is entered. If there is no Completion date entered this should be Orange until the current day lapses over the timescale for completion.
TIA