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
-
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
Thanks,
Anjanesh Vaidya
Smartsheet Development, Ignatiuz Software
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️
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 matthew.strehler@neweratech.com and we can set up a quick 30-minute call to walk through it.
Best,
Matthew Strehler | Senior Solutions Consultant
New Era Technology
Matthew.Strehler@NewEraTech.com | 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
-
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
Thanks,
Anjanesh Vaidya
Smartsheet Development, Ignatiuz Software
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️
-
Hi Anjanesh,
Yes, this has worked 🤩, thank you!
Kind regards,
Aiva
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!