Convert Text-Formatted "Day Name, Month Name, Date of Month, Year" to "YYYY-MM-DD" Date Format

Question

I have an Excel sheet with thousands of dates saved in Text format, written the following way:

I want these cells to be converted into "YYYY-MM-DD" format. However, Excel cannot detect these cells as Date Format; hence, when I try to use the "Format Cells" (or Ctrl+1) to change the date format, it does not work and stays the same. I need them to be in "YYYY-MM-DD" because I will be using the date in multiple functions throughout my workbook and the Text formatted Dates do not allow that.

Answer 1

You may try FILTERXML() in this way.

=FILTERXML("<t><s>"&SUBSTITUTE(A1,", ","</s><s>",1)&"</s></t>","//s[last()]")

Then format resulting cells as YYYY-MM-DD. Or you can use TEXT() function to get output directly as date.

=TEXT(FILTERXML("<t><s>"&SUBSTITUTE(A1,", ","</s><s>",1)&"</s></t>","//s[last()]"),"YYYY-MM-DD")

Answer 2

In addition to Harun's answer. For if one does not have Windows one could use: =TEXT(MID(A1,FIND(",",A1)+2,LEN(A1)),"yyyy-mm-dd")

Answer 3

However, you can also use the Text To Columns inbuilt feature of Excel From Data Tab to turn those text formatted dates to actual Excel Dates, please follow the steps,

• Select The Range, In Image Below, Its A:A,

• Next From Data Tab, Click Text To Columns

• In Text To Columns Wizard - Step 1 Of 3 - Click Fixed Width Under Original Data Type and Press Next

• In The Step 2 Of 3 - Move The Lines With Arrow As Shown In The Image Below & Press Next

Lines With Arrows(Signifies A Column Break)

• The Last & Final Step --> Step 3 Of 3, Under Data Preview, Select The Days Column And Click -- Do Not Import Column(Skip) From Above Column Data Format & Same Way For The Last Blank Column As Well, Now Click The Date Column And Select From Above Column Data Format as Date (MDY) & Press Finish!

• To Confirm Whether Those Have Been Converted To Actual Excel Date Or Not, You May Use A Formula Like Below Or While Changing The Date Format You Can Select General Or Number Category To Verify

FORMULA USED TO VERIFY IN CELL B1

=ISNUMBER(A1)

And You Are Done!