Convert to a Date

Options

So, I have what seems to be a simple date question, but I am unable to find a solution. I need to convert text into a date.

Example: 2023-09-01

Need to convert to 9/1/2023

Any ideas?

Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    Options

    Hi @Jeremy Norman,

    Something like this should work if your text date has the same format:

    =DATE(VALUE(LEFT([Text column]@row, 4)), VALUE(MID([Text column]@row, 6, 2)), VALUE(RIGHT([Text column]@row, 2)))

    If it didn't have the leading 0s for day/month then you could add in FINDs to get the relevant start points. I'm also not 100% sure which date format you're using so if month/day are the wrong way round then switch the 2nd and 3rd parts of the formula around!

    Hope this helps, but if you've any problems/questions then just post! πŸ™‚

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer βœ“
    Options

    @Jeremy Norman

    See below formula. Change the bolded references to match your data

    =DATE(VALUE(LEFT(Text@row, 4)), VALUE(MID(Text@row, 6, 2)), VALUE(RIGHT(Text@row, 2)))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!