How to turn a numerical text date into a date value
We use a form to gather registrations for events that are constantly changing. At any given time we will provide 5-8 dates to select from and update it every month with new dates. In the form, the participants must select the date they plan to attend from a dropdown. To my knowledge, I cannot actually do this with a date value, so the dropdown selection is entered into the sheet once submitted as a text column. The answer is in the format of 07/01/22
I need to somehow turn that into an actual date value in another column. Any ideas on where to start with this, or if I'm overlooking something obvious?
If more backstory is helpful- we have ~8 events broken down into 3 employee titles. Previously, we would instruct what dates were available per title and the submitter had to select the date themselves (the column supporting the question was a date value). We used automation to notify us if an incorrect date was selected. We then use an Index/collect formula to gather the dates from all titles into one column. We would like to simplify and just give the submitter the only options available via a dropdown, if we can somehow use helper columns to turn that selection into an actual date value.
Thanks so much for the help!
Help Article Resources
Check out the Formula Handbook template!