Weekly date with manual override
I'm looking to create a formula to have a cell show a date for when our next delivery comes in.
Typically these deliveries are every Tuesday, but sometimes they are late by 1 day, or we skip a week because we don't need it.
I would like a cell to show the next estimated delivery date
example.
Today the cell would show: "TODAY"
Tomorrow the cell would show: "Tuesday, Jan 2 2024" (we are skipping next week)
Again, once the day of delivery is here the cell will show "TODAY" and then the following day will show the next estimated delivery date.
I want to have 2 columns that are override columns, one that changes the date by one day, and the other by a week.
I'm really not sure where to even start with this formula so any insight is appreciated. Preferably an explanation as to how the formula works, so I can learn from it and help others.
Thanks!
Answers
-
Hi @Mark.Hendley, I modified a formula from this post:
Function to calculate date of next Monday — Smartsheet Community
... to get the formula for "Next Tuesday" (with the exception of Tuesday being "Today"):
=IF(WEEKDAY(TODAY()) = 3, "Today", IF(WEEKDAY(TODAY()) < 3, TODAY() + 9, IF(WEEKDAY(TODAY()) > 3, TODAY() + 10 - WEEKDAY(TODAY()))))
Does this get you far enough? If you need to add a week or day with an override column, you would just need to take the output of above and add 1 or 7 to it, but that gets into how your data and sheets is formatted.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!