Formulas and Functions

[Aaron Lockett]

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

[#2]

I've cracked it!

Changed the direction of the red to read less than instead of more than.

=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")))

Thanks for your patients and help guys!