Create dependency reminder by date

Greg Welch

I'm looking to create a content tracker that includes a reminder to review once a document has reached a certain age.

For example, Content piece one contains a column for "publish date", once 6 months have passed since that date, I'd like a status column with options of "current, needs review, outdated" to reflect status based on age (under 6 months current, over 6 months needs review, over 1 year outdated).

 

Is this possible?

Kennedy Stomps

Hi Greg-- You can definitely do this in Smartsheet! You'll first want to set up the reminder by creating a new Date type column in your sheet, then use a formula in that column to determine the Reminder date, something like:

 

=[Publish Date]1 + 180

 

This will create a Reminder date by adding 180 days to the date in the Publish Date column. Then, you can set up a Reminder based on the date in this column.

 

Next, you can set up a Status column-- I'd suggest a Dropdown column since it sounds like there's only three options available. You can use another formula to automatically determine what option fits the Publish date in each row, something like:

 

=IF(TODAY() - [Publish Date]11 <= 180, "Current", IF(AND(TODAY() - [Publish Date]11 >= 181, TODAY() - [Publish Date]11 < 365), "Needs Review", IF(TODAY() - [Publish Date]11 >= 365, "Outdated")))

 

Basically, if the difference between the current date and the Publish Date is less than 180 days, the status will show as "Current". If the difference is between 180 and 365 days, it will show as "Needs Review". If the difference is greater than 365 days, it will show as "Outdated". Hope this helps!