Recurring Weekly Tasks Formula

Wes S
Wes S
edited 12/09/19 in Formulas and Functions

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

Tags:

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

     

    [Last Completed Date]@row + 7

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    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?

     

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

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

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!