Conditional Formatting based on date

Hi Dayerx. I am an Excel user like you.

I am assuming that you are meaning to compare the dates in Column A with today's date on whatever day you are looking in the file. To do this you will need three Conditional Formatting rules, one for each color criteria. These must be entered individually one at a time.

I modified a couple of the dates in Column A to have dates in each category and to hit the 30 and 60 day marks precisely to confirm there is no overlap. In the screenshot below Columns E through G show the actual results of the three formulas, and Column I shows the actual number of days from the date in Column A to today that is being used to determine the Conditional Formatting.

Rule 1 for 30 days or less before today: Green.

=$A2>=TODAY()-30

Rule 2 for 31-60 days before today: Orange.

=AND($A2<TODAY()-30,$A2>=TODAY()-60)

Rule 3 for more than 60 days: Red.

=$A2<TODAY()-60

1. For each rule select all of the cells in Column C that you want formatted and on the Home ribbon click on Conditional Formatting>New Rule>Use a Formula.
2. Enter the formula for the rule you are entering in the Format Values line.
3. Click on the Format button at the lower right and in the Format Cells dialogue choose the appropriate color for that rule. Then Click OK and OK.

Actually, after some thought might I suggest adding one more rule.

Rule 1-Green: Next meeting due is not past due.

=EDATE($B2,2)>TODAY()-31

Rule 2-Yellow: Next meeting due is 1-30 days past due.

=AND(EDATE($B2,2)<TODAY(),EDATE($B2,2)>TODAY()-31)

Rule 3-Orange: Next meeting due is past due 31-60 days before today's date.

=AND(EDATE($B2,2)<TODAY()-30,EDATE($B2,2)>=TODAY()-60)

Rule 4-Red: Next meeting due is past due more than 60 days before today's date.

=EDATE($B2,2)<TODAY()-60

All other information noted above in my last reply is still the same.

I hope I understand now. Here are the formulas for the three rules.

Rule 1-Green: Next meeting due is either a future date or is within the last 30 days before today's date.

=EDATE($B2,2)>TODAY()-31

Rule 2-Yellow: Next meeting due is past due 31-60 days before today's date.

=AND(EDATE($B2,2)<TODAY()-30,EDATE($B2,2)>=TODAY()-60)

Rule 3-Red: Next meeting due is past due more than 60 days before today's date.

=EDATE($B2,2)<TODAY()-60

NOTE: I added the names in Column A per your descriptions in the last reply, so the formulas all refer to the Last Meeting Date being located in Column B. If it is in a different column than that adjust the Column references in the formulas appropriately.

I also modified the Last Meeting date in Row 3 to reflect a situation where the Next Meeting Date is past due by 5 days but still within the 30-day window.

As before, enter these as follows:

1. For each rule select all of the cells in Column C that you want formatted and on the Home ribbon click on Conditional Formatting>New Rule>Use a Formula.
2. Enter the formula for the rule you are entering in the Format Values line.
3. Click on the Format button at the lower right and in the Format Cells dialogue choose the appropriate color for that rule. Then Click OK and OK.

Additionally, the screenshot shows a formula in Column D that calculates the Next Meeting Due two months after the Last Meeting if that is of help.

=EDATE(B2,2)

Thank you Rich, I appreciate your responses

Excel is not my strength so apologies if I am confusing. It kind of looks like yours has worked Rich

What I am looking to do, is identify by colour the "next meeting date (Column c) is ok and does not need my attention (Green), which is due in the next 30 days (orange) and needs to be scheduled, and, which is well past the meeting due date and needs immediate attention (red).

I am required to meeting with staff every two months.

Example:

Row 2 - My last meeting with "Jo" was on the 15.01.2024 and is due for their next meeting on 15.03.2024. This would appear Green as it is not overdue based on todays date.

Row 3 - My last meeting with "Bob" was on the 16.12.2023 and is due for their next meeting on 16.02.2024. This would appear Green as it is also not overdue.

Row 4 - My last meeting with "Jenny" was 05.10.2023 and was due on 05.12.2023. this would be red as it is well past its due date.

Example: - My last meeting was with "Sally on 08.11.2024 and was due on 08.01.2024. This would be yellow as it is 30-60 days past the due date.

Hope that helps

I am assuming that the number of days between the dates in column C and column A are what you are referring to.

Refer to the screen shot below. I have changed some dates in column C so there are some matches for all conditions (colours).

I added date differences in column D just for testing. It is not required or used in the Conditional Formatting.

Following formulas provided so you can copy them (Difficult to copy from a screen shot).

Note that the formulas are relative addressing and entered as if they are for the first row of the "Applies To" range. Excel looks after the relative addressing for the remainder of the range.

Formula for Green is: =AND(NOT(ISBLANK(A2)),C2-A2<=30)

Formula for Orange is: =AND(NOT(ISBLANK(A2)),C2-A2>=30,C2-A2<=60)

Formula for Red is: =AND(NOT(ISBLANK(A2)),(C2-A2)>=60)

We could go further and test for a blank in either column A or Column C but I am assuming that if you enter a date in Column A then a date will be entered in column C. Note that the Applies to range is =$C$2:$C$17. (I enclosed this in a border). Edit the "Applies to" range for your project but first cell should be from row 2 where you have column headers above.