Forum Discussion

Aaron Lockett's avatar
Aaron Lockett
Brass Contributor
Feb 14, 2018

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

    • Aaron Lockett's avatar
      Aaron Lockett
      Brass Contributor

      Hi Ismael

       

      Thanks for your reply but this hasn't produced the outcome I was looking for but it has stemmed an idea.

       

      Thanks!

      • Ismael Pimienta's avatar
        Ismael Pimienta
        Copper Contributor

        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.

  • Ismael Pimienta's avatar
    Ismael Pimienta
    Copper Contributor

    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.

    • Aaron Lockett's avatar
      Aaron Lockett
      Brass Contributor
      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.
  • Aaron Lockett's avatar
    Aaron Lockett
    Brass Contributor

    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.

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP

      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()))

       

      • Aaron Lockett's avatar
        Aaron Lockett
        Brass Contributor
        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()))

Resources