How to turn a numerical text date into a date value

Amanda Alv

Hi all,

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!

Amanda

Andrée Starå

Hi @Amanda Alv

I hope you're well and safe!

Try something like this. (in your date column and update the column name to match yours)

=DATE(VALUE("20" + RIGHT(Date@row, 2)), VALUE(LEFT(Date@row, 2)), VALUE(MID(Date@row, 4, 2)))

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Amanda Alv

I hope you're well and safe!

Try something like this. (in your date column and update the column name to match yours)

=DATE(VALUE("20" + RIGHT(Date@row, 2)), VALUE(LEFT(Date@row, 2)), VALUE(MID(Date@row, 4, 2)))

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Amanda Alv

Thank you so much! That did the trick; really appreciate the help, especially over the holiday weekend!

Hope you enjoy the weekend!

Amanda

Andrée Starå

@Amanda Alv

Excellent! Thanks!

You're more than welcome!