Due Date Every Other Week on Friday

Options

Hello,

Thank you in advance for your help. I'm trying to create a column with a due date that will change frequently. We have tasks where we need updates that are due every two weeks on a friday. I've tried multiple ways to set up this formula, but have been unsuccessful. Is there a way to set up a formula so it shows the due date will be every other friday?

Thank you!

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @andhou

    There are a few ways to do this but what I would do is use a helper date column with a "Record a Date" workflow.

    Every week on Saturday, the Workflow will look into your sheet (see: Create a Time-Based Automated Workflow)

    And if the Friday date in the Due Date column is in the past...meaning it was the previous Friday, before today's date..

    then it will record that current Saturday's date into the Saturday helper column.


    This way, you have an ever-changing reference point for your formula. You can then base your Due Date off of the Saturday column and use a simple Column Formula of:

    =[Saturday Date]@row + 13

    To always get the Next Friday date, 2 weeks after the last due date past.

    This does mean when you're entering new tasks you'll have to enter the Saturday 2 weeks beforehand to see the correct first Due Date in your Friday Column (you can't have both a column formula and manual input in the same column).

    Let me know if this makes sense and works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @andhou

    There are a few ways to do this but what I would do is use a helper date column with a "Record a Date" workflow.

    Every week on Saturday, the Workflow will look into your sheet (see: Create a Time-Based Automated Workflow)

    And if the Friday date in the Due Date column is in the past...meaning it was the previous Friday, before today's date..

    then it will record that current Saturday's date into the Saturday helper column.


    This way, you have an ever-changing reference point for your formula. You can then base your Due Date off of the Saturday column and use a simple Column Formula of:

    =[Saturday Date]@row + 13

    To always get the Next Friday date, 2 weeks after the last due date past.

    This does mean when you're entering new tasks you'll have to enter the Saturday 2 weeks beforehand to see the correct first Due Date in your Friday Column (you can't have both a column formula and manual input in the same column).

    Let me know if this makes sense and works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • andhou
    Options

    Hi Genevieve,

    Thank you so much for the detailed explanation! I sincerely appreciate it!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!