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

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

SlowdownSteve

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

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å

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!

SlowdownSteve

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å

@SlowdownSteve

Excellent!

Happy to help!