Spreadsheet conditional

New Contributor
Hello everyone.

I have made a spreadsheet which shows, in date, upcoming and expired dates highlighted in green (Greater than =Today()+30),
yellow (Between = Today() and =Today+30) and red (Less Than =Today() ) respectively.

My question is:

Is it possible to format the cells to automatically change to the due dates required when I enter the date the competency was achieved?

For example: I achieved a certain competency today which will expire in 120 days. I enter todays date into cell E1. Can I format cell E1 to automatically update to 120 days from today? Instead of having to manually work out 120 days from now. (I can work out how to do it using 2 cells and the Edate setting)
I would also need the colour formatting ( in date, due and expired) to still work.

Hope that makes sense.
Many thanks

I am using Office 365.
2 Replies
best response confirmed by Ian1103 (New Contributor)


Automatically having a date that you enter is not a good idea - it can easily lead to errors.

If you accidentally click in the formula bar and press Enter without changing anything, another 4 months would be added to the date, without a trace of what happened.


Instead, enter the achievement day in one cell, for example E1, and use a formula to calculate the expiration date in another cell, for example F1. This leaves you with a visible check that the dates are 4 months apart.

Makes sense.

Thanks for your help.