How do I automatically update a target date?

Mark Strecker

I have a Smartsheet in which one field has the current date using the TODAY function, and the next field has a target date. A third field that is in a countdown column subtracts the target date from the current date. When it reaches 0, an automation sends an email. Is there a way to set up an automation that will update the target date by one year?

Kelly Moore

@Mark Strecker

Actually building on @ker9 suggestion, run the Record Date automation 365d after your Target Date is set. You'll have to pre-populate it once with the correct dates - a simple copy paste in and out of excel will do it quickly. Then set your automation to run. Insert the recorded date back into the Target Date.

Kelly

Deonizia Egan

take the "today" field and add 365?

=sum(TODAY@row+365) where "today" is the column name of the field you are using. set as a column formula so that it auto populates each new row.

Mark Strecker

Alas, it doesn't work on a date field.

Kelly Moore

Hey @Mark Strecker

You shouldn't have to use the SUM function. You can just add 365 to TODAY(). Or, you can add 365 to the Today Field that you created.

=TODAY()+365

=TODAY(365)

=[your TODAY field]@row + 365

Any one of the above formulas should work for you.

Kelly

Mark Strecker

I should probably explain a bit more what I need. The TODAY function won't work in this case for two reasons. 1. Cells with it won't auto-update when the sheet is closed, which will be most the time. This I solved by using an automation the puts the current dates in a given column in all the fields that aren't blank. 2. Adding +365 adds 365 days, but each day it changes. The number I want to change automatically each year is static. In 2025, for example, the target day might be 2-1-25. The next year it will be 2-1-26. What I want is an automated way to add one year to that day at the end of each year. In other words, on 12-31-25, I want an automation to add one year to the target dates.

Kelly Moore

Hey @Mark Strecker

Sorry Mark for my confusion.

To my knowledge, it is not yet possible to use automation to accomplish what you need. One cannot yet insert formulas, references to other cells, or into formulas from automation.

As an alternative, you could use a Summary Field on your sheet to achieve your dynamic value. Could you have a Summary Field to subtract the current year from 2024, which would advance your static date each year. I called this field [Target Year Addition]. (I suggest a Summary Field vs a Helper column or in-formula reference so that the sheet does not have to complete the subtraction calculation on every row. Since you already have automation that refreshes the sheet, the Summary field should work nicely).

[Target Year Addition]

=YEAR(TODAY())-2024

Then the Target Date becomes

=DATE(YEAR(Date@row) + [Target Date Addition]#, MONTH(Date@row), DAY(Date@row))

Would this work for you?
Kelly

ker9

@Mark Strecker

I suggest not using the TODAY function but using automation to update a column with the date. Set to run every night after midnight.

The TODAY function is volatile.

I create a column named AutoToday as a Date column. Automation suggestion below. Use the AutoToday date in your formulas. Sheet does not need to be open for it to update.

Hope this helps!

Kelly Moore

@Mark Strecker

Actually building on @ker9 suggestion, run the Record Date automation 365d after your Target Date is set. You'll have to pre-populate it once with the correct dates - a simple copy paste in and out of excel will do it quickly. Then set your automation to run. Insert the recorded date back into the Target Date.

Kelly

Mark Strecker

Thanks everyone for your suggestions. I think the final and easiest solution, then, is to create an automation for each target date and have the automation update the it to the current date on the target date's day. If, for eg., the target date is 2/1/25, then on 2/1/26 the automation will update that target date to 2/1/26.