Convert Text to Date

Options

I am looking for help to convert 20230818 ['Max Date' text formatted column] to read 08/18/2023 in the ['Last Activity Date' date formatted column]. However, the formula that I am using returns a value of #INCORRECT ARGUMENT SET

=DATE(VALUE(MID([Max Date]@row, 5, 2), (VALUE(RIGHT([Max Date]@row, 2), (VALUE(LEFT([Max Date]@row, 4)))))))

Best Answer

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @Heather P.

    This should work:

    =DATE(VALUE(LEFT([Max Date]@row, 4)), VALUE(MID([Max Date]@row, 5, 2)), VALUE((RIGHT([Max Date]@row, 2))))

    Your formula will not work as the middle bit of the formula is trying to calculate the month - there isn't an 18th month of the year. The format of the date itself will be based on Smartsheet settings.

  • Frank Falco
    Frank Falco ✭✭✭✭✭✭
    Options

    Try this in the Last Activity Date (Which should be a DATE Column):

    =DATE(VALUE(LEFT([Max Date]@row, 4)), VALUE(MID([Max Date]@row, 5, 2)), VALUE(RIGHT([Max Date]@row, 2)))

    DATE (YYYY,MM,DD)

    Need to wrap VALUE() around to convert string to number for the DATE() function

    Your version was:

    DATE (MM,DD,YYYY)


    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Heather P.,

    Try this:

    =RIGHT((LEFT([Max Date]@row, 6)), 2) + "/" + RIGHT([Max Date]@row, 2) + "/" + LEFT([Max Date]@row, 4)

    I tested and here are the results:


  • Heather P.
    Options

    Hi @Ray Lindstrom - that worked! Thank you so much!

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Options

    So glad to hear it. Happy to help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!