Issues with Auto-Update Dates
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!
Best Answers
-
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)
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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)
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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.
-
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)
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå @Paul Newcome That worked! I appreciate your help.
-
Happy to help. 👍
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
