 Highlighted
Contributor

# Formulas and Functions

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

20 Replies
Highlighted

# Re: Formulas and Functions

Does anyone have any light to shed on this?
Highlighted

# Re: Formulas and Functions

Try:

=IF(K2="","D",IF(L2="",IF(K2<NOW(),"R","A"),IF(K2<L2,"R","G")))

Highlighted

# Re: Formulas and Functions

Hi Ismael

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

Thanks!

Highlighted

# Re: Formulas and Functions

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.

Highlighted

# Re: Formulas and Functions

The outcome I'm expecting is that Column M to turn red if dates specified in Column K lapse over the current date. Regardless whether a date has been entered into Column L.

Once I figure it out I will post the formula.
Highlighted

# Re: Formulas and Functions

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

Highlighted

# Re: Formulas and Functions

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.

Highlighted

# Re: Formulas and Functions

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.

Highlighted

# Re: Formulas and Functions

In the morning I will remove all confidential data and provide a sample for you.

Thanks.

Highlighted

# Re: Formulas and Functions

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.

Highlighted

# Re: Formulas and Functions

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"

Highlighted

# Re: Formulas and Functions

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.
Highlighted

# Re: Formulas and Functions

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

Highlighted

# Re: Formulas and Functions

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

# Re: Formulas and Functions

Aaron, do you mean to use exactly the same formula for all rules?

Highlighted

# Re: Formulas and Functions

I mean how do I translate the rules you have stipulated into a formula for Column M - What is the exact formula I need to enter into Column M to produce my intended outcome?

TIA
Highlighted

# Re: Formulas and Functions

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

Highlighted

# Re: Formulas and Functions

It does. If L is past K it resutls in R.

Highlighted

# Re: Formulas and Functions

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