Automatically Update a date when it's in the past

Chelsea_Clayton

Hello,

I'm trying to create a formula or workflow rule that updates a date column when the date is in the past. I work on annual renewals so I want the renewal date to change from 9/1/2020 to 9/1/2021 after 9/1/2020 is in the past.

I was able to create a formula in another column that adds 365 days to the renewal date but I don't want an extra column just for this function if I can avoid it and I need to account for leap year.

Is there a way to say, IF Renewal Date is in the past, add one year? or IF Renewal Date < Today's Date, Renewal Date + 1 year?

David Joyeuse

Hi @Chelsea_Clayton

This will require some help here and there as you cannot do a count in cells in smartsheet as you'd like to do it. Even adding an helper column won't help as you'll end up into circular references.

But there's some workaround to have this working.

1st, we're gonna need an helper column I'd called 'Past renewal Date', which is a checkbox or flag type column. With this formula:

=IF([Renewal Date]@row < TODAY(), 1, 0)

Then have some automated workflow base on the checkbox column. And copy the row to another sheet when the box is checked.

Finally, in your renewal date column use this formula:

=DATE(YEAR(MAX({Renewal Date - Second Sheet Column}))+1, MONTH(MAX({Renewal Date - Second Sheet Column})), DAY(MAX({Renewal Date - Second Sheet Column})))

That should do the trick. But you'll probably need to add in some COLLECT function for the MAX function, otherwise you'll have the same date in all your renewal date cells :)