Forum Discussion

Copper Contributor

Apr 03, 2025

Expiry date help.

Hi, i am looking for a formula to turn a cell different colours as a test expiry date approaches. I have column D with the date the test was taken. I then have column E with the formula =IF(D3=""...

Formulas and Functions

office 365

Mike762

Copper Contributor

Apr 03, 2025

Hi Hans,

Thank you for replying,

Just so ive got this correct this formula;

=AND($E3<>"", $E3<=EDATE(TODAY(), 3))

Willl format the change for a 3 month warning? And if i wanted to use it for a 2 month or a 30 day would i just correct the formula as below?

=AND($E3<>"", $E3<=EDATE(TODAY(), 2))

=AND($E3<>"", $E3<=EDATE(TODAY(), 1))

Again i appreciate you taking the time to reply.

HansVogelaar

MVP

Apr 03, 2025

Yes, indeed.

Mike762
Copper Contributor
Apr 06, 2025
Hi Hans, the formula seems to work however, i want to show it as overdue as well. Would this just be a case of using conditonal formatting to change it red with the below formula?
=AND($E3<>"", $E3=EDATE(TODAY())
Ive been playing and cant seem to get it to work.

Thank you in advance.
Mike762
Copper Contributor
Apr 03, 2025
Amazing!! Thank you so much for your help 👍 ill try it Tomorrow and mark as solution