Hi, i can no longer format cells in a column to go red if the date is yesterday or older, could someone assist please?

Question

Hi, i can no longer format cells in a column to go red if the date is yesterday or older, could someone assist please?

Jenny Lingenhult 0

I have created a caseload spreadsheet with appointment dates and phone calls where I need to be able to see clearly if the date has past, ie the cell goes red. I managed to format one of the columns and this works perfectly but I cannot seem to make it happen for the above use. I have followed videos but the sheet stays the same no matter what I do.

Please advise

Barry Schwarz 4,476 Reputation points

2025-11-08T05:46:30+00:00

Are their any other conditional formatting rules that affect the cells in question?

1 answer

Your answer

Barry Schwarz 4,476 Reputation points

2025-11-08T05:46:30+00:00

Are their any other conditional formatting rules that affect the cells in question?

Answer 1

Hi @Jenny Lingenhult,
Thank you for posting your question to Microsoft Q&A Forum.

I've tested your question on my side and has achieve your requirement by doing the following steps:

Select the column or range that contains your dates (e.g., Column B).
Go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Enter this formula:
```
   =AND(ISNUMBER(B1), B1<=TODAY()-1) 
```
- Replace B1 with the first cell in your selected range.
- TODAY()-1 means yesterday. If you want “yesterday or older,” this works perfectly.
Click Format, choose Fill Color: Red, then OK.
Apply the rule.

For this solution to work you have to ensure 2 things:

ISNUMBER(B1) ensures the cell contains a date.
B1<=TODAY()-1 checks if the date is yesterday or earlier.

If it doesn't work, I'd recommend you checking the requirement belows:

Cell format: Make sure the cells are actual dates, not text.
Date format: Make sure the dates are in the same format DD/MM/YY or MM/DD/YY. Wrong format can cause conflicts like the "9/11/2025" in the example image.
Absolute vs relative references: Use relative references (like B1) when applying to a range.
Multiple rules: If you have other conditional formatting rules, ensure they don’t override this one.

Note: Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.

Thank you for your patience and your understanding. If you have any questions, please feel free to reach out.

I'm looking forward to your reply.

If the answer is helpful, click "Accept Answer" and vote positively. If you have more questions about this answer, click "Comment".

Note: Follow the steps in our documentation to enable email notifications if you want to receive email notifications related to this topic. User's image

Share via

Hi, i can no longer format cells in a column to go red if the date is yesterday or older, could someone assist please?

1 answer

Your answer