Converting a text date: Thu, Aug 12, 2021 into real date

Options

Hi Everyone,

could anyone assist me with converting a text date column formatted like this: Thu, Aug 12, 2021 into a Date column?

For example, I have two columns.

  1. text date column where I am importing data in this format: Thu, Aug 12, 2021
  2. Real date column that forces a "real date"

I know I can use the =DATE(VALUE(LEFT([text date])@row .... VALUE(MID([text date])@row ...VALUE(RIGHT([textdate])


I am just having a hard time piecing it all together as I probably would have to also convert the months into actual numbers right right? Something like Jan = 01 , Aug=02 etc..

Thanks for any help!

Best Answer

  • Lidiya Shutaya
    Lidiya Shutaya ✭✭✭
    Answer ✓
    Options

    You are correct - you will need the months to be actual numbers and I would recommend making a look up table for the month.

    **I made an assumption that all the months will be 3 characters, if not, may need to tweak it a little bit**

    Parsed out the formulas are the following:

    Year: =VALUE(RIGHT([Primary Column]@row, 2))

    Month: =VALUE(VLOOKUP(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 2, 3), [Column3]3:[Column4]14, 2, false))

    Day: =VALUE(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 6, 2))


    If you wanted to go directly from Thu, Aug 12, 2021 to 8/12/21 the formula is below:


    =DATE(VALUE(RIGHT([Primary Column]@row, 4)), VALUE(VLOOKUP(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 2, 3), [Column3]3:[Column4]14, 2, false)), VALUE(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 6, 2))

    I would just put the month name to number conversion on a separate sheet and insert reference in the VLOOKUP formula to make it easier :) (Would replace [Column3]3:[Column4]14 with whatever your reference sheet range is)

    Also, make sure you final date value column is set to DATE instead of Text/Number.

    Let me know if you have any additional questions!

    Best,

    Lidiya Shutaya

Answers

  • Lidiya Shutaya
    Lidiya Shutaya ✭✭✭
    Answer ✓
    Options

    You are correct - you will need the months to be actual numbers and I would recommend making a look up table for the month.

    **I made an assumption that all the months will be 3 characters, if not, may need to tweak it a little bit**

    Parsed out the formulas are the following:

    Year: =VALUE(RIGHT([Primary Column]@row, 2))

    Month: =VALUE(VLOOKUP(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 2, 3), [Column3]3:[Column4]14, 2, false))

    Day: =VALUE(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 6, 2))


    If you wanted to go directly from Thu, Aug 12, 2021 to 8/12/21 the formula is below:


    =DATE(VALUE(RIGHT([Primary Column]@row, 4)), VALUE(VLOOKUP(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 2, 3), [Column3]3:[Column4]14, 2, false)), VALUE(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 6, 2))

    I would just put the month name to number conversion on a separate sheet and insert reference in the VLOOKUP formula to make it easier :) (Would replace [Column3]3:[Column4]14 with whatever your reference sheet range is)

    Also, make sure you final date value column is set to DATE instead of Text/Number.

    Let me know if you have any additional questions!

    Best,

    Lidiya Shutaya

  • SlowdownSteve
    Options

    Hey Lidiya,


    thanks so much for the help. So I am just going to Freeze and hide my look up column to convert.

    I am trying to apply it as a formula for the entire column:

    =DATE(VALUE(RIGHT([Imported Date]@row, 4)), VALUE(VLOOKUP(MID([Imported Date]@row, FIND(",", [Imported Date]@row) + 2, 3), [Column3]3:[Column4]14, 2, false)), VALUE(MID([Imported Date]@row, FIND(",", [Imported Date]@row) + 6, 2)))

    If I paste it individually into the cell, i get the the INVALID DATA TYPE error because the Imported Data is blank. I also can't apply it as a column formula because of a syntax error


  • Lidiya Shutaya
    Options

    Hello,

    Putting an IFERROR option should eliminate the blank rows coming up as #invalid data type.

    =IFERROR(DATE(VALUE(RIGHT([Test 1]@row, 4)), VALUE(VLOOKUP(MID([Test 1]@row, FIND(",", [Test 1]@row) + 2, 3), [Column3]4:[Column4]15, 2, false)), VALUE(MID([Test 1]@row, FIND(",", [Test 1]@row) + 6, 2))), "")

    For the column formula issue - column formulas do not allow specific cell reference. It will allow @cell, @row, or a referenced range.

    =DATE(VALUE(RIGHT([Imported Date]@row, 4)), VALUE(VLOOKUP(MID([Imported Date]@row, FIND(",", [Imported Date]@row) + 2, 3), [Column3]3:[Column4]14, 2, false)), VALUE(MID([Imported Date]@row, FIND(",", [Imported Date]@row) + 6, 2)))

    If you put the lookup table in a separate sheet and use the "Reference sheet" feature, the syntax error should be resolved.

    Let me know if you add any additional questions!

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

    Hi @SlowdownSteve

    I hope you're well and safe!

    I'd recommend testing with a simpler formula first and convert it to a column formula and then change it to the other one and see if that works.

    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 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.

  • SlowdownSteve
    Options

    Just wanted to follow up and mentioned that it's working!

    Made a separate sheet like Lidiya mentioned and just called it from my main sheet.

    =DATE(VALUE(RIGHT([Imported Date]@row, 4)), VALUE(VLOOKUP(MID([Imported Date]@row, FIND(",", [Imported Date]@row) + 2, 3), {Date converter Range 1}, 2, false)), VALUE(MID([Imported Date]@row, FIND(",", [Imported Date]@row) + 6, 2))) 

    Thanks you Lidiya! Also appreciate the input Andree!

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

    @SlowdownSteve

    Excellent!

    Happy to help!

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!