Recurring Weekly Tasks Formula

Wes S

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

Paul Newcome

If it is always the same day of the week, you could use 

 

[Last Completed Date]@row + 7

Wes S

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

Paul Newcome

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?

 

Wes S

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) 

Paul Newcome

Glad you were able to find a working solution. That's what matters.