Date Formulas

Options

Hi all,


Attempting to create due dates for types of tasks within a grid. I will settle for a formula that adds days. But would really love to create automations based on selections made in the types of task column.


Please help😬

Answers

  • Genevieve P.
    Options

    Hi @ExecDirector

    The way I would do this is to have three columns in my sheet that are automatically populated:

    • 1 - Today's Date when a task is created/requested
    • 2 - Number of Working Days until due date (dependent on Task Type)
    • 3 - Due Date column with formula


    Column 1 would be automatically populated with a Record a Date workflow.


    Then Column 2 could be automatically populated through a Change Cell workflow with conditions identifying the correct number of days.


    Finally, Column 3 would have a column formula looking at the created date, then adding the number of working days to find the Due Date.

    Something like this:

    =IFERROR(WORKDAY([Date Submitted]@row, [Working Days]@row), "")


    You could skip the second workflow and helper "Working Days" column and write it all into one formula, if you'd prefer. This would be a nested IF statement identifying how many working days you want to add based on the Task Type selection. For example:

    =IFERROR(IF([Task Type]@row = "Type 1", WORKDAY([Date Submitted]@row, 1), IF([Task Type]@row = "Type 2", WORKDAY([Date Submitted]@row, 5), IF([Task Type]@row = "Type 3", WORKDAY([Date Submitted]@row, 15)))), "")


    Cheers,

    Genevieve

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!