Hello I am trying to create a formula that returns a specific date when a dropdown selection is made
class requested | start date |
---|---|
Jan 13, 2025 - July 14, 2025 Oconomowoc, WI | |
Jan 13, 2025 - July 11, 2025 Huntsville, AL (new site) | |
Feb 10, 2025 - Aug 11, 2025 Amsterdam, NY | |
Mar 24, 2025 - Sept 22, 2025 Fontana, CA | |
Apr 21, 2025 - Oct 20, 2025 Oconomowoc, WI | |
May 13, 2025 - Nov 17, 2025 Huntsville, AL | |
Jun 16, 2025 - Dec 15, 2025 Amsterdam, NY | |
Jul 28, 2025 - Feb 22, 2026 Fontana, CA | |
Aug 25, 2025 - Mar 2, 2026 Oconomowoc, WI | |
Sep 22, 2025 - Mar 30, 2026 Huntsville, AL | |
Oct 20, 2025 - Apr 27, 2026 Amsterdam, NY |
Best Answers
-
Hi @Attilio
If you're trying to get the date shown in the class requested column, you will need to have three helper columns. One to capture the month, which you can get using
=IF(LEFT([class requested]@row, 3) = "Jan", 1, IF(LEFT([class requested]@row, 3) = "Feb", 2, IF(LEFT([class requested]@row, 3) = "Mar", 3, IF(LEFT([class requested]@row, 3) = "Apr", 4, IF(LEFT([class requested]@row, 3) = "May", 5, IF(LEFT([class requested]@row, 3) = "Jun", 6, IF(LEFT([class requested]@row, 3) = "Jul", 7, IF(LEFT([class requested]@row, 3) = "Aug", 8, IF(LEFT([class requested]@row, 3) = "Sep", 9, IF(LEFT([class requested]@row, 3) = "Oct", 10, IF(LEFT([class requested]@row, 3) = "Nov", 11, IF(LEFT([class requested]@row, 3) = "Dec", 12))))))))))))
One to capture the date, which you can get using
=VALUE(MID([class requested]@row, 5, 2))
One to capture the year, which you can get using
=VALUE(MID([class requested]@row, 9, 4))
Once you have the three columns with values, you can use this formula in your start date column =DATE([Year helper column]@row, [Month helper column]@row, [Day helper column]@row)
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
Hello @AravindGP
Thank you for the response this helps. i can get this formula to work
=IF([Class Requested]@row = "Jan 13, 2025 - July 14, 2025 Oconomowoc, WI", "1/13/25")
and it will display the date i want it to which is 1/13/25, so i guess my issue is how to put together multiple if statements so if someone selected one of the 11 choices from class requested it shows the start date i specified since the dates will not change. so if someone selected " Oct 20, 2025 - Apr 27, 2026 Amsterdam, NY" it would show "10/20/25" in the start date column. Thank you!
Answers
-
Hi @Attilio
If you're trying to get the date shown in the class requested column, you will need to have three helper columns. One to capture the month, which you can get using
=IF(LEFT([class requested]@row, 3) = "Jan", 1, IF(LEFT([class requested]@row, 3) = "Feb", 2, IF(LEFT([class requested]@row, 3) = "Mar", 3, IF(LEFT([class requested]@row, 3) = "Apr", 4, IF(LEFT([class requested]@row, 3) = "May", 5, IF(LEFT([class requested]@row, 3) = "Jun", 6, IF(LEFT([class requested]@row, 3) = "Jul", 7, IF(LEFT([class requested]@row, 3) = "Aug", 8, IF(LEFT([class requested]@row, 3) = "Sep", 9, IF(LEFT([class requested]@row, 3) = "Oct", 10, IF(LEFT([class requested]@row, 3) = "Nov", 11, IF(LEFT([class requested]@row, 3) = "Dec", 12))))))))))))
One to capture the date, which you can get using
=VALUE(MID([class requested]@row, 5, 2))
One to capture the year, which you can get using
=VALUE(MID([class requested]@row, 9, 4))
Once you have the three columns with values, you can use this formula in your start date column =DATE([Year helper column]@row, [Month helper column]@row, [Day helper column]@row)
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
Hello @AravindGP
Thank you for the response this helps. i can get this formula to work
=IF([Class Requested]@row = "Jan 13, 2025 - July 14, 2025 Oconomowoc, WI", "1/13/25")
and it will display the date i want it to which is 1/13/25, so i guess my issue is how to put together multiple if statements so if someone selected one of the 11 choices from class requested it shows the start date i specified since the dates will not change. so if someone selected " Oct 20, 2025 - Apr 27, 2026 Amsterdam, NY" it would show "10/20/25" in the start date column. Thank you!
-
Hi @Attilio
My previous response will help get all the start dates currently and in future as it is picking out the values from your text (which could change for future events). We can write nested IF as well, but it is a bit rigid. If you prefer the nested if, you can continue writing the second statement after the true value of the first, like this
=IF([Class Requested]@row = "Jan 13, 2025 - July 14, 2025 Oconomowoc, WI", "1/13/25", IF([Class Requested]@row = "Oct 20, 2025 - Apr 27, 2026 Amsterdam, NY", "10/20/25, IF([Class Requested]@row = "Jan 13, 2025 - July 11, 2025 Huntsville, AL (new site)", "1/13/25"
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
Hello @AravindGP
Thank you very much for all the help I tried both ways and really liked how the first one worked and has flexibility for the future. Its was actually also easier to use. Thank you again for the help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!