Recurring Weekly Tasks Formula
I have a sheet set up with a weekly task, a column for last completed date, and a column for next due date. I have a column that indicates the day of week that it must be completed on.
I am trying to set up a formula in the next due date column that uses the selected day of week to update the next due date based on the last completed date. Something like: =IF(TODAY() > [last completed date], next WEEKDAY([day of week due]))
I'm just having trouble figuring out how to express the value if true portion of the formula.
Thanks in advance for your help.
Wes
Comments
-
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
Thanks for the reply. Some tasks may be completed early or late so using that formula would potentially skew upcoming due dates. Any other thoughts?
Thanks,
Wes
-
Ok. So if it is due on Wednesday but isn't completed until Thursday then it would be [Last Completion Date] + 6. Got it.
In a date column you can enter the first three digits of the weekday and it will populate the date of that weekday within the current week.
Monday of this week was 06/17/19, so if you just type into a date cell "mon" (without the quotes), once you leave that cell it becomes "06/17/19" (without the quotes of course).
Taking advantage of that, if a task is due on Mondays, you can enter "mon + 7" (without the quotes), and that will automatically give you next Monday's date.
Mon
Tue
Wed
Thu
Fri
Sat
Sun
Would be the entries for each day of the week. Would this work for you?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I figured out a great solution to it. This formula references the column with the provided day of week number and always updates to the next upcoming date.
=IF(WEEKDAY([Most Recent Date Completed]2) = 1, [Most Recent Date Completed]2 - (WEEKDAY([Most Recent Date Completed]2) - [Day of Week Due]2), [Most Recent Date Completed]2 - (WEEKDAY([Most Recent Date Completed]2) - [Day of Week Due]2) + 7)
-
Glad you were able to find a working solution. That's what matters.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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!