How do you conditionally format a spreadsheet to highlight a deadline date involving variou dates 18 months prior to expiry.

Dear Linda

Thank you for attending Excel Intermediate training. I hope you enjoyed the training and benefited from the course.

Please refer to the Excel file that I have uploaded with this post.

If you notice only one cell has different formatting because I have used the Conditional formatting on all the cells in column D to have all the cells to have light yellow background and Blue font colour if the date is 18 months from the current date.

The condition that I have applied is =TODAY()+540

Please note that I have typed in 540 instead of 18 Months as dates are treated as numbers in Excel.

18 Months * 30 days = 540

Today will ensure that the date automatically updates everyday and you have the correct cells highlighted.

I hope this works.

Please let me know if this has helped in resolving your query.


If this has answered your query then I would request you to please mark the question as resolved!! If not and you have a specific question related to this then please post it as a new question and we should be able to provide you the solution for it!!

Kindest Regards


Rajeev Rawat
MOS Master Instructor 2000 and 2003

Attached files...

Conditional Formatting 18 month from current date.xls