How to turn a numerical text date into a date value

Options

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

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!