Formula to calculate new date from dropdown

Aiva Saltyte

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?

Anjanesh Vaidya

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

Matt___New_Era_Tech

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 matthew.strehler@neweratech.com and we can set up a quick 30-minute call to walk through it.

Best,

Aiva Saltyte

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

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

Aiva Saltyte

Hi Anjanesh,

Yes, this has worked 🤩, thank you!

Kind regards,

Aiva