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 :)

Andrée Starå

Hi@Chelsea_Clayton

To add to David's excellent advice.

Please have a look at my post below with a method I developed that you could combine it with.

More info:

Hi all,<\/p>

I hope you're well and safe!<\/p>

I've developed a solution that lets you store the date or value.<\/strong><\/p>

You'd use the copy-row automation and a VLOOKUP or combination of INDEX\/MATCH to make it work.<\/p>

We'd trigger the copy-row to another sheet and get the created date\/other value and then use the VLOOKUP\/INDEX\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\/value that we can use, it will work.<\/p>

Also, this opens up more options with auto-numbering and similar.<\/p>

Depending on other workflows, you could reuse one sheet for multiple workflows.<\/p>

I hope that helps!<\/p>

Let me know if you have any questions!<\/p>

Stay safe and have a fantastic day!<\/p>

Best,<\/p>

Andrée Starå<\/p>

Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>","bodyRaw":"[{\"insert\":\"Hi all,\\n\\nI hope you're well and safe!\\n\\nI've developed a solution that lets you \"},{\"attributes\":{\"bold\":true},\"insert\":\"store the date or value.\"},{\"insert\":\"\\n\\nYou'd use the copy-row automation and a VLOOKUP or combination of INDEX\\\/MATCH to make it work.\\nWe'd trigger the copy-row to another sheet and get the created date\\\/other value and then use the VLOOKUP\\\/INDEX\\\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\\\/value that we can use, it will work.\\n\\nAlso, this opens up more options with auto-numbering and similar.\\nDepending on other workflows, you could reuse one sheet for multiple workflows.\\n\\nI hope that helps!\\n\\nLet me know if you have any questions!\\n\\nStay safe and have a fantastic day!\\n\\nBest,\\n\\nAndrée Starå\\n\\nWorkflow Consultant \\\/ CEO @ \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/workbold.com\\\/\"},\"insert\":\"WORK BOLD\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-05-24T11:36:34+00:00","insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/68589\/lock-or-store-date-value-solution-without-using-zapier","embedType":"quote","name":"Lock or Store Date\/Value Solution without using Zapier"}"> https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå| Workflow Consultant / CEO @WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!