Forum Discussion
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="","",EDATE(D3,6)) which will autopopulate the cell with an expiry date 6 months in the future of the date i manually input into column D.
I am looking for a way for the cell to automatically change colour when the expiry date is 3 months away, 1 month away and overdue.
I have seen a formula using the TODAY function but wouldnt that only work if there is already a date in the expiry date cell? And not if the expiry is blank until its autopopulated from Cell D.
Hope this makes sense
Thank you in advance
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.
Yes, indeed.
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.
Create a conditional formatting rule with formula
=AND($E3<>"", $E3<=EDATE(TODAY(), 3))
or alternatively
=AND($D3<>"", $D3<=EDATE(TODAY(), -3))
