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

SlowdownSteve

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!

Lidiya Shutaya

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