Hello,

I am trying to set up conditional formatting to track due dates.
There will be different types of items being tracked, each with their own "duration". I have been able to set up basic conditional formatting.
Shading cells green when the deadline has not passed. Yellow when within a amount of days to the deadline, and Red once the deadline has passed.

For the affected cells I have set up the formatting as such. (there is a cell named "Deadline" for each type of item. YT is the yellow time that has not yet been determined for each item)

Use formula to determine which cells to format
GREEN -> =NOW()< Deadline - YT
YELLOW -> =AND(NOW()>=( Deadline - YT), NOW()<= Deadline)
RED -> =NOW()> Deadline


My challenge is that these things will be tracked Monthly/Quarterly/Annually depending on the item. But what I have setup will only work for one iteration.

Is it possible to encode something that will update the deadline date after a certain point, making this document more viable for long-term use?

I want to avoid doing it manually since there are things with different timelines.


Thank you for any help!