Formula to calculate new date from dropdown

Hi, I have a column where a manager gives a timeframe of '1 week', '2 weeks' or '1 month' to complete a given task from their visit - how do I get smartsheets to generate a new date through a formula with one of the above timeframes chosen from a drop down and show new due date in 'To be completed by column' which has a date only setting?


Best Answer

  • Anjanesh Vaidya
    Anjanesh Vaidya ✭✭✭✭
    Answer ✓

    Hi Aiva Saltyte,

    You can try the below formula for the required condition in the "To be completed by" column which will be a Date column.

    =IF([To be completed in]@row = "1 Week", [Date raised]@row + 7, IF([To be completed in]@row = "2 Weeks", [Date raised]@row + 14, IF([To be completed in]@row = "1 Month", [Date raised]@row + 30, " ")))

    I hope it will work.

    Thank you!

    Anjanesh Vaidya

Answers

  • Hi Aiva,

    To achieve this, you would need a column that houses a date from which the "To be completed by" date can be calculated. Once you have set up that column, you would use a series of "IF" statements combined with references to the column that was added to calculate the ultimate date that would show in the "To be completed by" column.

    If you have any questions, please feel free to reach out to me at [email protected] and we can set up a quick 30-minute call to walk through it.

    Best,

    Matthew Strehler | Senior Solutions Consultant

    New Era Technology

    [email protected] | neweratech.com

  • Hi Matthew,

    Thanks, I have a column 'Date raised' for when rows are added - so the date would be calculated of that column - are you able to share a start of IF formula for '1 week' calculation? I want to give that a go first and if does not work, will reach out for a call

  • Anjanesh Vaidya
    Anjanesh Vaidya ✭✭✭✭
    Answer ✓

    Hi Aiva Saltyte,

    You can try the below formula for the required condition in the "To be completed by" column which will be a Date column.

    =IF([To be completed in]@row = "1 Week", [Date raised]@row + 7, IF([To be completed in]@row = "2 Weeks", [Date raised]@row + 14, IF([To be completed in]@row = "1 Month", [Date raised]@row + 30, " ")))

    I hope it will work.

    Thank you!

    Anjanesh Vaidya

  • Hi Anjanesh,

    Yes, this has worked 🤩, thank you!

    Kind regards,

    Aiva

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!