Formula that will allow a date in text format to be captured in a date format

I have a Date getting imported as 08JUN2021 (2charDay+3charMonth+4charYear) and I need to switch it to a Date format that will allow integration in other formulas and logic flows.

So far I have tried these 2 formulas without success:


=DATE(VALUE(RIGHT([WES Evaluable Report Date]@row, 4)), VALUE(VLOOKUP(MID([WES Evaluable Report Date]@row, FIND("", [WES Evaluable Report Date]@row) + 2), [Column56]1:[Column57]12, 2, false)), VALUE(MID([WES Evaluable Report Date]@row, FIND("", [WES Evaluable Report Date]@row), 3)))

(Incorrect Argument)

=DATE(VALUE(RIGHT(LEFT([WES Evaluable Report Date]@row, 9), 4)), INDEX([Column56]1:[Column57]12, MATCH(MID([WES Evaluable Report Date]@row, 3, 3), [Column56]1:[Column57]12)), VALUE(LEFT(LEFT([WES Evaluable Report Date]@row, 9), 2)))

(Invalid Data Type)

Any help is really appreciated it!!



Best Answers

  • Radu Antonie
    Radu Antonie ✭✭
    Answer ✓

    Problem solved!


    =IFERROR(DATE(VALUE(RIGHT([WES Evaluable Report Date]@row, 4)), VALUE(INDEX([Month #]$1:[Month #]$12, MATCH(MID([WES Evaluable Report Date]@row, 3, 3), [Month Text]$1:[Month Text]$12, 0))), VALUE(LEFT([WES Evaluable Report Date]@row, 2))), "")


    This still does not allow for column formula but it works.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Radu Antonie

    I'm glad you figured it out! Thanks for posting your solution.

    If you wanted to turn this into a Column Formula, you could make a second sheet with just two columns: the Month Name as Text and the corresponding Month Number. Then you could change the INDEX(MATCH portion of your formula to look across sheets into this other sheet.

    This would give you {cross sheet references} instead of a specific table reference with row numbers so you could turn it into a Column Formula.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Radu Antonie
    Radu Antonie ✭✭
    Answer ✓

    Problem solved!


    =IFERROR(DATE(VALUE(RIGHT([WES Evaluable Report Date]@row, 4)), VALUE(INDEX([Month #]$1:[Month #]$12, MATCH(MID([WES Evaluable Report Date]@row, 3, 3), [Month Text]$1:[Month Text]$12, 0))), VALUE(LEFT([WES Evaluable Report Date]@row, 2))), "")


    This still does not allow for column formula but it works.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Radu Antonie

    I'm glad you figured it out! Thanks for posting your solution.

    If you wanted to turn this into a Column Formula, you could make a second sheet with just two columns: the Month Name as Text and the corresponding Month Number. Then you could change the INDEX(MATCH portion of your formula to look across sheets into this other sheet.

    This would give you {cross sheet references} instead of a specific table reference with row numbers so you could turn it into a Column Formula.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!