Automatically update Start and Finish dates with new week
Hi,
Does anyone know if it is possible to automatically update Start and Finish dates to the new week once it hits Monday?
For example, I think the formula should somehow state "When the date changes to XXX (Every Monday), change Start and Finish to Today and Today (+5)"
Not sure if this is even possible? It would save my team from having to update the dates every week since the tasks recur weekly, but also allow the tasks to be included on their "Due in 7 Days" report.
Thanks for the help!
Best Answer
-
The way I would do this would be to use a nested IF statement to check what day of the week Today is using the WEEKDAY function, then return the 2nd day of this week (Monday) based on Today.
For example, you would say, if Today is Thursday, return Today minus three days:
=IF(WEEKDAY(TODAY()) = 5, TODAY(-3)
You would do this for each potential day of the week, to have one long formula based on each day.
Monday Formula:
=IF(WEEKDAY(TODAY()) = 1, TODAY(1), IF(WEEKDAY(TODAY()) = 2, TODAY(), IF(WEEKDAY(TODAY()) = 3, TODAY(-1), IF(WEEKDAY(TODAY()) = 4, TODAY(-2), IF(WEEKDAY(TODAY()) = 5, TODAY(-3), IF(WEEKDAY(TODAY()) = 6, TODAY(-4), IF(WEEKDAY(TODAY()) = 7, TODAY(-5))))))))
Then in the Friday column you'd do the same thing, but adjust the returned date to always be Friday.
For example, you would say, if Today is Thursday, return Today plus one day:
=IF(WEEKDAY(TODAY()) = 5, TODAY(1)
Friday Formula:
=IF(WEEKDAY(TODAY()) = 1, TODAY(5), IF(WEEKDAY(TODAY()) = 2, TODAY(4), IF(WEEKDAY(TODAY()) = 3, TODAY(3), IF(WEEKDAY(TODAY()) = 4, TODAY(2), IF(WEEKDAY(TODAY()) = 5, TODAY(1), IF(WEEKDAY(TODAY()) = 6, TODAY(), IF(WEEKDAY(TODAY()) = 7, TODAY(-1))))))))
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
The way I would do this would be to use a nested IF statement to check what day of the week Today is using the WEEKDAY function, then return the 2nd day of this week (Monday) based on Today.
For example, you would say, if Today is Thursday, return Today minus three days:
=IF(WEEKDAY(TODAY()) = 5, TODAY(-3)
You would do this for each potential day of the week, to have one long formula based on each day.
Monday Formula:
=IF(WEEKDAY(TODAY()) = 1, TODAY(1), IF(WEEKDAY(TODAY()) = 2, TODAY(), IF(WEEKDAY(TODAY()) = 3, TODAY(-1), IF(WEEKDAY(TODAY()) = 4, TODAY(-2), IF(WEEKDAY(TODAY()) = 5, TODAY(-3), IF(WEEKDAY(TODAY()) = 6, TODAY(-4), IF(WEEKDAY(TODAY()) = 7, TODAY(-5))))))))
Then in the Friday column you'd do the same thing, but adjust the returned date to always be Friday.
For example, you would say, if Today is Thursday, return Today plus one day:
=IF(WEEKDAY(TODAY()) = 5, TODAY(1)
Friday Formula:
=IF(WEEKDAY(TODAY()) = 1, TODAY(5), IF(WEEKDAY(TODAY()) = 2, TODAY(4), IF(WEEKDAY(TODAY()) = 3, TODAY(3), IF(WEEKDAY(TODAY()) = 4, TODAY(2), IF(WEEKDAY(TODAY()) = 5, TODAY(1), IF(WEEKDAY(TODAY()) = 6, TODAY(), IF(WEEKDAY(TODAY()) = 7, TODAY(-1))))))))
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Genevieve,
Thank you, I think this will work great!
-
No problem! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!