Creating Helper Column to Set a Date to reoccur annually

Day Osborne

I am working on a project that tracks quarterly and annual filings. The date will vary depending upon the type of filing. I want to set it up so that reminders are sent automatically without a specific year in place. For example, a corporate annual report reminder date should be 6/30. Every year on 6/30, we want an automated notification to go to a designated person to file this annual report.

We are trying to avoid having to manually reset the year to the next year each time. I have been reading on the community site that helper columns can be set up to achieve this. However, I can't figure out how they work or how to set them up.

Any help is very much appreciated.

Michael Culley

I would do this:

=DATE(YEAR(TODAY()), MONTH(date@row), DAY(date@row))

So whatever date is in that row it will change the year to the current year, then set the reminder off of that date.

Ryan Sides

@Day Osborne

Hi Day, If I'm understanding your requirement correctly, you're wanting Smartsheet to kick off an alert to someone to file their annual report. You want this alert to be sent on 6/30 each year, correct?

Create a Column and call it "Date to Send Report". Add a column formula to it:

=DATE(YEAR(TODAY()), 6, 30)

Add an alert automation so that when 6/30 hits in the current year, the alert is sent. The "YEAR" function in the formula will change each year automatically.

Here's the automation:

Does that help?

Ryan

Day Osborne

@Ryan Sides - Thank you for your help. I put the formula in the helper column but it is showing as "INVALID COLUMN VALUE".

Ryan Sides

Did you format your new column as a "DATE" not a text?

Day Osborne

That worked. When does the year change? it is showing as 6/30/2022 which is in the past.

Ryan Sides

It will change on 1/1/23

Day Osborne

Thank you. How do you get it to change once the task has been completed for the year meaning the task was completed 6/30/22, but now it needs to be completed 6/30/23. Is there a way to set up a formula that allows it to change before 1/1/23?

Day Osborne

@Ryan Sides How do you get it to change once the task has been completed for the year meaning the task was completed 6/30/22, but now it needs to be completed 6/30/23. Is there a way to set up a formula that allows it to change before 1/1/23?

Ryan Sides

Hi Day,

The following formula will set the date to 6/30 of the current year, unless the current date is after 6/30, then it will set it to 6/30 of the following year:

=IF(MONTH(TODAY()) < 7, DATE(YEAR(TODAY()), 6, 30), DATE(YEAR(TODAY()) + 1, 6, 30))

Can you tell me the purpose of this field and what you are trying to accomplish with your sheet? I went at this from answering your question, but that may not be solving your real problem. Thanks!

-Ryan

Ryan Sides

@Day Osborne Hi Day, just checking in. Did this work for you? - Ryan