Issues with Auto-Update Dates

Asha Krishnan

I have a project schedule with start and end dates listed for each phase of the project. (Phase 1: Start Date - 2/1/2023, End Date - 3/1/2023). I am experiencing issues with the year automatically updating in the date columns when a new year begins. I want the year in the date columns to automatically update to the current year while keeping the day and the month the same. (Phase 1: Start Date - 2/1/2024, End Date - 3/1/2024). Is there a way this can be accomplished?

Thank you for your help!

Paul Newcome

Try this:

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

The above would go into a column separate from your existing End column, and you can use the End column to select a date that has the appropriate month/day combo.

If you wanted it in the End column, you would need to "hard code" the month and day.

=DATE(YEAR(TODAY()), 01, 01)

Andrée Starå

https://community.smartsheet.com/discussion/comment/391254#Comment_391254

Try something like this.

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

Did that work as expected?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

https://community.smartsheet.com/discussion/109073/issues-with-auto-update-dates

Hi,

I hope you're well and safe!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Asha Krishnan

@Andrée Starå Thank you for your response! I have included a screenshot for your reference.

When a new year (2024) begins, I want the year in the date columns to automatically update to the current year while keeping the day and the month the same. (Phase 1: Start Date - 1/1/2024, End Date - 2/1/2024, Phase 2: Start Date - 2/1/2024, End Date - 3/1/2024)

Andrée Starå

https://community.smartsheet.com/discussion/comment/391170#Comment_391170

Happy to help!

Can you share the Formula you're testing?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Asha Krishnan

@Andrée Starå I used the following formula to obtain the current year while keeping the day and month the same at the beginning of a new year (January 1).

=IF(AND(MONTH(TODAY()) = 1, DAY(TODAY()) = 1), DATE(YEAR([End]@row) + 1, MONTH([End]@row), DAY([End]@row)))

I am not sure how to have the new dates auto-populate in the same date columns in the next year.

Paul Newcome

Try this:

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

The above would go into a column separate from your existing End column, and you can use the End column to select a date that has the appropriate month/day combo.

If you wanted it in the End column, you would need to "hard code" the month and day.

=DATE(YEAR(TODAY()), 01, 01)

Andrée Starå

https://community.smartsheet.com/discussion/comment/391254#Comment_391254

Try something like this.

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

Did that work as expected?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Asha Krishnan

@Andrée Starå @Paul Newcome That worked! I appreciate your help.

Paul Newcome

Happy to help. 👍

Andrée Starå

@Asha Krishnan

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!