Conditional Formatting Issue

I am having a conditional formatting problem. I have to format an Excel sheet to show eval completion dates with the following parameters: <30 green, 30-60 days yellow, over 60 days red, and it has to be from the date of the anniversary date but only by the month and day. Plus the Eval completed date has to be able to track not only the month and day, but year by the anniversary date. I can't get the formula right.

Please Login or Register  to view this content.

The appointment date does not play much factor and would only confuse the equation.

Thank you.

Your example is incorrect. 2/3 is more than 30 days after 12/5 so should be yellow. 6/13 is almost a year after 6/15 so should be red. I have attached a file with a solution and more complete data.

There is one rule for red and one rule for green. I have defaulted the cell color to yellow so no color is needed for yellow; if it's not red or green, it will be yellow be default.

Originally Posted by KMBLUM72

I tried this and it didn't work. I attached the worksheet to see if you have some luck.

There is no conditional formatting in this file, so I don't know why it didn't work for you. It works for me. Column M is just for reference and can be deleted. Blank cells in column J will be yellow by default. Let me know if you want something different.

Give me one or two specific examples so I can troubleshoot.

Originally Posted by KMBLUM72

should all be red because they are outside of the year timeframe.

What do you mean by the year timeframe? You originally said

<30 green, 30-60 days yellow, over 60 days red

Just looking at line 3

	H	I	J
1	Anniversary Date	Appointment Date	PDP Eval Completed Date
3	06/16/1997	06/16/2022	06/17/2020

6/17 is only 1 day later than 6/16 so should be green. Please explain why this should be red. I'm not going look at other dates until your requirements are clear.

Can you give a more rigorous explanation of your requirement? First you said only by month and day, now somehow the year comes into play.

Let's take a look at row 4, which you said gives the incorrect color.

	H	I	J
1	Anniversary Date	Appointment Date	PDP Eval Completed Date
4	10/05/1998	02/13/2023	11/01/2022

If the evaluation is not done within 60 days of that day within that calendar year

Within what calendar year?

In row 4, the Anniversary Date is 10/5/1998, and the PDP Eval Completed Date is in 2022, so the due date in that calendar year would be 10/5/2022. The completed date here is 11/1/2022, which is 27 days after the due date. Therefore it is less than 30 days and should be green.

Please explain where I went wrong in this explanation.

For example, line 3, 4, 6, and 11 should all be red

Well, now I'm just lost. That example I just did was row 4.

You still need to define your requirement regarding the year.

OK let's look at row 6, which has a date in 2020.

	H	I	J
1	Anniversary Date	Appointment Date	PDP Eval Completed Date
6	04/17/2006	05/04/2015	06/01/2020

In row 6, the Anniversary Date is 4/17/2006, and the PDP Eval Completed Date is in 2020 so the due date in that calendar year would be 4/17/2020. The completed date here is 6/1/2020, which is 45 days after the due date. Therefore it is 30-60 days after the due date and should be yellow.

Please explain where I went wrong in this explanation.

OK. I have been trying to pull this out like a bad tooth but here is what I think your requirement is. Please comment:

We are trying to determine the number of days elapsed since the Due Date for an evaluation. The Due Date for this purpose is the most recent date with the same month and day as the Anniversary Date. Examples:

Anniversary Date	Due Date
6/19/1995	6/19/2022
6/16/1997	6/16/2022
10/5/1998	10/5/2022
2/3/1999	2/3/2023
4/17/2006	4/17/2022
4/30/2014	4/30/2022
10/18/2022	10/18/2022
3/18/2020	3/18/2022
7/2/2001	7/2/2022
10/2/2003	10/2/2022
4/8/2013	4/8/2022
5/22/2008	5/22/2022
10/21/2010	10/21/2022
10/21/2010	10/21/2022
3/8/2017	3/8/2023
8/23/2021	8/23/2022
7/18/2011	7/18/2022

You did not state that anywhere but I am trying to read between the lines.

ALSO

All of your dates are text. My formulas are still working with that but whenever you have dates in Excel you should use date values, not text.

The cells are formatted to be dates, but the data that was put into them is text.

Now that we have defined what is a "due date" please explain what is the elapsed time you want to measure to determine the color--what date minus what date? I don't understand the overall problem you are trying to solve.

Here is the formula to determine the due date:

Values as displayed

	H	I	J	K	L	M
1	Anniversary Date	Appointment Date	PDP Eval Completed Date	PDP Expectations Set Date		Due Date
2	06/19/1995	04/16/2022	12/11/2020	01/14/2020		6/19/2022

Underlying formulas

	H	I	J	K	L	M
1	Anniversary Date	Appointment Date	PDP Eval Completed Date	PDP Expectations Set Date		Due Date
2	06/19/1995	04/16/2022	12/11/2020	01/14/2020		=IF(DATE(YEAR(TODAY()),MONTH(H2),DAY(H2))>TODAY(),DATE(YEAR(TODAY())-1,MONTH(H2),DAY(H2)),DATE(YEAR(TODAY()),MONTH(H2),DAY(H2)))

if a PDP is not done within 60 days of the due date of the calendar year

We're going in circles. What do you mean by "of the calendar year"?

Most of your PDP dates are more than a year old so be definition they will be red.

I figured out what's been bugging me about this. It seems like this has to somehow take into account today's date.

If this data were mine, here is what I would want to see:

If there has been no PDP eval completed since the last due date, turn the date red if the due date was more than 60 days before today's date.

See attached for that implementation and let me know if that helps.

How do you define past due? What if the PDP Eval date was just a few days after the due date? It was late, but it's not longer due. What if it was a few days before the due date? That means it was done so shouldn't be due, but what do you consider the dividing line between being early for the latest due date (and therefore fulfilled the requirement) vs. being late for the prior one (therefore the current one is still due)?

We have to get from the abstract concept of "I want anything past due to be red" to define in very concrete terms how we look at these dates and determine what is considered "past due." You can imagine why it is complicated to build big systems.