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

Screen Shot 2019-06-19 at 1.22.49 PM.png

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…
INDEX/COLLECT??
Having trouble with an INDEX/COLLECT/CONTAINS formula. I have two sheets, my source and my destination; the source sheet has a column I want to collect into the destination. -Tool ID, is in both sheets and the target of the INDEX/MATCH, the source sheet might have this listed 6 or 7 times -Purpose of Device, there are…