Adding at risk based on health color

bobloblaw

I'm trying to make a formula to set a flag in the at risk column based on whether the health is red and at least 3 days past the due date. Something isn't behaving quite right on the 3 days part and it's flagging some things that are not yet 3 days past due....

Here is what I have, is this not correct?

=IF(AND((Health@row = "Red"), [End Date]@row < TODAY(3)), 1, 0)

JamesB

@bobloblaw

I am not sure the formula you have for your health column, but I recreated the referenced columns in your formula and I do not see any issues with the corrected formula @DKazatsky2 provided to meet your criteria as detailed out in your initial request.

JamesB

@bobloblaw You have extra parentheses in your AND statement. Try this:

=IF(AND(Health@row = "Red", [End Date]@row < TODAY(3)), 1, 0)

DKazatsky2

@bobloblaw

Aside from the extra parentheses mentioned above, I believe you are also checking the end date against 3 days from now, as opposed to 3 days ago.

Give this a try: =IF(AND(Health@row = "Red", [End Date]@row < TODAY(-3)), 1, 0)

Hope this helps,

Dave

bobloblaw

Thank you both! I will give these a shot!

bobloblaw

Looks like neither worked.

JamesB

@bobloblaw Can you share a screenshot of your sheet with confidential info removed/hidden of course.

bobloblaw

DKazatsky2

@bobloblaw

Have you tried the -3 as suggested above? What behavior are you seeing that needs to be corrected?

JamesB

@bobloblaw

I am not sure the formula you have for your health column, but I recreated the referenced columns in your formula and I do not see any issues with the corrected formula @DKazatsky2 provided to meet your criteria as detailed out in your initial request.

JamesB

@DKazatsky2 Please know that I am complimenting you not critiquing. I used your formula as the example because it met the original request.

DKazatsky2

@JamesB Thanks. Never took it negatively.

bobloblaw

Thank you all - I think I had a typo in my column!! This is why I should lean on copy/paste instead of just typing them over and over! 🤣