Extracting Date from Drop Down Column to a Date Column
I have a drop down column with the dates formatted as "Saturday, November 23, 2019" so people can select class dates. I need to convert those to an actual date column so I can sort by date in a report but I cannot figure out the proper formula. Any help would be great. Thanks.
Comments
-
First thing to do is parse the data out.
.
To get the year:
=VALUE(RIGHT([Dropdown Column]@row, 4)
.
Month would be:
=MID([Dropdown Column]@row, FIND(" ", [Dropdown Column]@row) + 1, FIND(" ", [Dropdown Column]@row, FIND(" ", [Dropdown Column]@row) + 1) - FIND(" ", [Dropdown Column]@row))
.
Day would be:
=VALUE(LEFT(RIGHT([Dropdown Column]@row, 8), 2)))
.
Then we would convert the Month text to a number. There are a few ways to do this. The most straightforward being a nested IF statement along the lines of:
=IF(Month@row = "January", 1, IF(Month@row = "February", 2, IF(Month@row = "March", 3, .......................................................))))))))))))
and continuing the pattern for all twelve months.
.
Then you would enter something like this into the new date type column.
=DATE(Year@row, [Month Number]@row, Day@row)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks. I just made a look up table and ran it that way.
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
- 302 Events
- 33 Webinars
- 7.3K Forum Archives