# How to Convert Text to Date Format (100 examples)

There are many different ways to write a date. If you get a list of dates to convert them into another formats, you may first check the "Format cells" method. If this method does not work, your dates are more likely in the text format.

The combinations of the year, the month, the day and the separators can be endless. The following are 100 common date text formats and the formulas to convert them into the real dates. After you convert the text into the date format, you can use the format cells method again to transfer to the format you want.

Group 1: 10 digits text with 4 digits year (click the text date to have formula)

Group 2: 6 digits text with 2 digits year (click the text date to have formula)

Group 3: 9 digits with 3 letters month (click the text date to have formula)

Group 4: 9 digits with 3 letters month and time (click the text date to have formula)

Group 5: 3 letters month and weekday (click the text date to have formula)

Group 6: 3 letters month weekday and time (click the text date to have formula)

Group 7: Full month name and 4 digits year (click the text date to have formula)

Group 8: Full month name, 4 digits year and time (click the text date to have formula)

Group 9: Full month and weekday name (click the text date to have formula)

Group 10: Full month weekday name and time (click the text date to have formula)

The important thing when converting text to date is to extract the year, the month and day, then combine together by using the DATE function.

Group 1: 10 digits with 4 digits year

The text contains 10 digits with a 4 digits year, but the year, the month and the day are in different orders with different separators including spaces.

Example 1: text date "01252015" in cell A1

Step 1: To first extract the year, the month and the day;

• Year: =RIGHT(A1,4), which is 2015
• Month: =LEFT(A1,2), which is 01
• Day: =MID(A1,3,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

= DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2))

Step 3: Change cell A1 in the formula and change the date format by using the "format cells" method.

Example 2: Text date "01.25.2015" in cell A1

Step 1: To first extract the year, the month and the day;

• Year: =DATE(RIGHT(A1,4), which is 2015
• Month: =LEFT(A1,2), which is 01
• Day: =MID(A1,4,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))

Step 3: Change cell A1 and change the date format by using the "format cells" method.

Example 3: Text date "01-25-2015" in cell A1

Step 1: To first extract the year, the month and the day;

• Year: =RIGHT(A1,4), which is 2015
• Month: =LEFT(A1,2), which is 01
• Day: =MID(A1,4,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 4: Text "01/25/2015" in cell A1

Step 1: To first extract the year, the month and the day;

• Year: =RIGHT(A1,4), which is 2015
• Month: =LEFT(A1,2), which is 01
• Day: =MID(A1,4,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 5: Text "01 25 2015" in cell A1

Step 1: To first extract the year, the month and the day;

• Year: =RIGHT(A1,4), which is 2015
• Month: =LEFT(A1,2), which is 01
• Day: =MID(A1,4,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 6: Text "20150125" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MID(A1,5,2), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 7: Text date "2015.01.25" in cell A1

Step 1: To first extract the year, the month and the day;

• Year: =LEFT(A1,4), which is 2015
• Month: =MID(A1,6,2), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 8: Text date "2015-01-25" in cell A1

Step 1: To first extract the year, the month and the day;

• Year: =LEFT(A1,4), which is 2015
• Month: =MID(A1,6,2), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 9: Text date "2015/01/25" in cell A1

Step 1: To first extract the year, the month and the day;

• Year: =LEFT(A1,4), which is 2015
• Month: =MID(A1,6,2), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 10: Text date "2015 01 25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MID(A1,6,2), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Group 2: 6 digits text with 2 digits year

The text contains 6 digits with a 2 digits year, but the year, the month and the day are in different orders with different separators including spaces.

Example 1: Text date "012515" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,2)+2000, which is 2015
• Month: =LEFT(A1,2), which is 01
• Day: =MID(A1,3,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,3,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 2: Text date "01.25.15" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,2)+2000, which is 2015
• Month: =LEFT(A1,2), which is 01
• Day: =MID(A1,4,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 3: Text date "01-25-15" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,2)+2000, which is 2015
• Month: =LEFT(A1,2), which is 01
• Day: =MID(A1,4,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 4: Text date "01/25/15" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,2)+2000, which is 2015
• Month: =LEFT(A1,2), which is 01
• Day: =MID(A1,4,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 5: Text date "01 25 15" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,2)+2000, which is 2015
• Month: =LEFT(A1,2), which is 01
• Day: =MID(A1,4,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 6: Text date "150125" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,2)+2000, which is 2015
• Month: =MID(A1,3,2), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,2)+2000,MID(A1,3,2),RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 7: Text date "15.01.25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,2)+2000, which is 2015
• Month: =MID(A1,4,2), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,2)+2000,MID(A1,4,2),RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 8: Text date "15-01-25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,2)+2000, which is 2015
• Month: =MID(A1,4,2), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,2)+2000,MID(A1,4,2),RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 9: Text date "15/01/25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,2)+2000, which is 2015
• Month: =MID(A1,4,2), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,2)+2000,MID(A1,4,2),RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Example 10: Text date "15 01 25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,2)+2000, which is 2015
• Month: =MID(A1,4,2), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,2)+2000,MID(A1,4,2),RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "format cells" method.

Group 3: 9 digits with 3 letters month

The text contains 9 digits with a 3 letters month, but the year, the month and the day are in different orders with different separators including spaces.

Example 1: Text date "Jan252015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,3)&"1")), which is 01
• Day: =MID(A1,4,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4),MONTH(DATEVALUE(LEFT(A1,3)&"1")),MID(A1,4,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 2: Text date "Jan.25.2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,3)&"1")), which is 01
• Day: =MID(A1,5,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1), which is 25 (if the text is Jan.8.2015, the result will return 8; if the text is Jan.08.2015, the result will return 08)

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(LEFT(A1,3)&"1")), MID(A1,5, FIND(".",A1,FIND(".",A1)+1) – FIND(".",A1)-1))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 3: Text date "Jan-25-2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,3)&"1")), which is 01
• Day: =MID(A1,5,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1), which is 25 (if the text is Jan-8-2015, the result will return 8; if the text is Jan-08-2015, the result will return 08)

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(LEFT(A1,3)&"1")), MID(A1,5,FIND("-",A1, FIND("-",A1)+1) – FIND("-",A1)-1))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 4: Text date "Jan/25/2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,3)&"1")), which is 01
• Day: =MID(A1,5,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1), which is 25 (if the text is Jan/8/2015, the result will return 8; if the text is Jan/08/2015, the result will return 08)

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(LEFT(A1,3)&"1")), MID(A1,5, FIND("/",A1, FIND("/",A1)+1) – FIND("/",A1)-1))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 5: Text date "Jan 25, 2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,3)&"1")), which is 01
• Day: =MID(A1,5,FIND(",",A1)-FIND(" ",A1)-1), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(LEFT(A1,3)&"1")), MID(A1,5, FIND(",",A1) – FIND(" ",A1)-1))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 6: Text date "25Jan2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,3,3)&"1")), which is 01
• Day: =LEFT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(MID(A1,3,3)&"1")), LEFT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 7: Text date "25.Jan.2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)&"1")), which is 01 (if the text is 8.Jan.2015, the result will return 01; if the text is 08.Jan.2015, the result will return 01)
• Day: =LEFT(A1,FIND(".",A1)-1), which is 25 (if the text is 8.Jan.2015, the result will return 8; if the text is 08.Jan.2015, the result will return 08)

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(MID(A1, FIND(".",A1)+1,FIND(".",A1, FIND(".",A1)+1)-FIND(".",A1)-1)&"1")), LEFT(A1, FIND(".",A1)-1))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 8: Text date "25-Jan-2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1)&"1")), which is 01 (if the text is 8-Jan-2015, the result will return 01; if the text is 08-Jan-2015, the result will return 01)
• Day: =LEFT(A1,FIND("-",A1)-1), which is 25 (if the text is 8-Jan-2015, the result will return 8; if the text is 08-Jan-2015, the result will return 08)

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(MID(A1, FIND("-",A1)+1,FIND("-",A1, FIND("-",A1)+1) – FIND("-",A1)-1)&"1")), LEFT(A1, FIND("-",A1)-1))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 9: Text date "25/Jan/2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&"1")), which is 01 (if the text is 8/Jan/2015, the result will return 01; if the text is 08/Jan/2015, the result will return 01)
• Day: =LEFT(A1,FIND("/",A1)-1), which is 25 (if the text is 8/Jan/2015, the result will return 8; if the text is 08/Jan/2015, the result will return 08)

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(MID(A1, FIND("/",A1)+1, FIND("/",A1, FIND("/",A1)+1) -FIND("/",A1)-1)&"1")), LEFT(A1, FIND("/",A1)-1))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 10: Text date "25 Jan, 2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,FIND(" ",A1)+1,FIND(",",A1)-FIND(" ",A1)-1)&"1")), which is 01 (if the text is 8 Jan, 2015, the result will return 01; if the text is 08 Jan, 2015, the result will return 01)
• Day: =LEFT(A1,FIND(" ",A1)-1), which is 25 (if the text is 8 Jan, 2015, the result will return 8; if the text is 08 Jan, 2015, the result will return 08)

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(MID(A1, FIND(" ",A1)+1, FIND(",",A1) – FIND(" ",A1)-1)&"1")), LEFT(A1, FIND(" ",A1)-1))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Group 4: 9 digits with 3 letters month and time

The text contains 9 digits with a 3 letters month plus time, but the year, the month and the day are in different orders with different separators including spaces.

Example 1: Text date "2015Jan25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,5,3)&"1")), which is 01
• Day: =MID(A1,8,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1,5,3)&"1")), MID(A1,8,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 2: Text date "2015.Jan.25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,6,3)&"1")), which is 01
• Day: =MID(A1,10,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1,6,3)&"1")), MID(A1,10,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 3: Text date "2015-Jan-25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,6,3)&"1")), which is 01
• Day: =MID(A1,10,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1,6,3)&"1")), MID(A1,10,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 4: Text date "2015/Jan/25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,6,3)&"1")), which is 01
• Day: =MID(A1,10,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1,6,3)&"1")), MID(A1,10,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 5: Text date "2015 Jan 25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,6,3)&"1")), which is 01
• Day: =MID(A1,10,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1,6,3)&"1")), MID(A1,10,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 6: Text date "Jan252015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,6,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,3)&"1")), which is 01
• Day: =MID(A1,4,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,6,4), MONTH(DATEVALUE(LEFT(A1,3)&"1")), MID(A1,4,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 7: Text date "Jan.25.2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,8,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,3)&"1")), which is 01
• Day: =MID(A1,5,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,8,4), MONTH(DATEVALUE(LEFT(A1,3)&"1")), MID(A1,5,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 8: Text date "Jan-25-2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,8,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,3)&"1")), which is 01
• Day: =MID(A1,5,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,8,4), MONTH(DATEVALUE(LEFT(A1,3)&"1")), MID(A1,5,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 9: Text date "Jan/25/2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,8,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,3)&"1")), which is 01
• Day: =MID(A1,5,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,8,4), MONTH(DATEVALUE(LEFT(A1,3)&"1")), MID(A1,5,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 10: Text date "Jan 25 2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,8,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,3)&"1")), which is 01
• Day: =MID(A1,5,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,8,4), MONTH(DATEVALUE(LEFT(A1,3)&"1")), MID(A1,5,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Group 5: 3 letters month and weekday

The text contains 3 letters month and weekday, but the year, the month, the day and the weekday are in different orders with different separators including spaces.

Example 1: Text date "Thu2015Jan25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,4,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,8,3)&"1")), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,4,4), MONTH(DATEVALUE(MID(A1,8,3)&"1")), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 2: Text date "Thu.2015.Jan.25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,5,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,10,3)&"1")), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,5,4), MONTH(DATEVALUE(MID(A1,10,3)&"1")), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 3: Text date "Thu-2015-Jan-25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,5,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,10,3)&"1")), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,5,4), MONTH(DATEVALUE(MID(A1,10,3)&"1")), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 4: Text date "Thu/2015/Jan/25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,5,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,10,3)&"1")), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,5,4), MONTH(DATEVALUE(MID(A1,10,3)&"1")), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 5: Text date "Thu 2015 Jan 25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,5,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,10,3)&"1")), which is 01
• Day: =RIGHT(A1,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,5,4), MONTH(DATEVALUE(MID(A1,10,3)&"1")), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 6: Text date "ThuJan252015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,4,3)&"1")), which is 01
• Day: =MID(A1,7,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(MID(A1,4,3)&"1")), MID(A1,7,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 7: Text date "Thu.Jan.25.2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,5,3)&"1")), which is 01
• Day: =MID(A1,9,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(MID(A1,5,3)&"1")), MID(A1,9,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 8: Text date "Thu-Jan-25-2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,5,3)&"1")), which is 01
• Day: =MID(A1,9,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(MID(A1,5,3)&"1")), MID(A1,9,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 9: Text date "Thu/Jan/25/2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,5,3)&"1")), which is 01
• Day: =MID(A1,9,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(MID(A1,5,3)&"1")), MID(A1,9,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 10: Text date "Thu Jan 25 2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,5,3)&"1")), which is 01
• Day: =MID(A1,9,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(MID(A1,5,3)&"1")), MID(A1,9,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Group 6: 3 letters month weekday and time

The text contains 3 letters month and weekday plus time, but the year, the month, the day and the weekday are in different orders with different separators including spaces.

Example 1: Text date "Thu2015Jan25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,4,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,8,3)&"1")), which is 01
• Day: =MID(A1,11,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,4,4), MONTH(DATEVALUE(MID(A1,8,3)&"1")), MID(A1,11,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 2: Text date "Thu.2015.Jan.25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,5,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,10,3)&"1")), which is 01
• Day: =MID(A1,14,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,5,4), MONTH(DATEVALUE(MID(A1,10,3)&"1")), MID(A1,14,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 3: Text date "Thu-2015-Jan-25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,5,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,10,3)&"1")), which is 01
• Day: =MID(A1,14,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,5,4), MONTH(DATEVALUE(MID(A1,10,3)&"1")), MID(A1,14,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 4: Text date "Thu/2015/Jan/25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,5,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,10,3)&"1")), which is 01
• Day: =MID(A1,14,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,5,4), MONTH(DATEVALUE(MID(A1,10,3)&"1")), MID(A1,14,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 5: Text date "Thu 2015 Jan 25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,5,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,10,3)&"1")), which is 01
• Day: =MID(A1,14,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,5,4), MONTH(DATEVALUE(MID(A1,10,3)&"1")), MID(A1,14,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 6: Text date "ThuJan252015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,9,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,4,3)&"1")), which is 01
• Day: =MID(A1,7,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,9,4), MONTH(DATEVALUE(MID(A1,4,3)&"1")), MID(A1,7,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 7: Text date "Thu.Jan.25.2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,12,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,5,3)&"1")), which is 01
• Day: =MID(A1,9,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,12,4), MONTH(DATEVALUE(MID(A1,5,3)&"1")), MID(A1,9,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 8: Text date "Thu-Jan-25-2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,12,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,5,3)&"1")), which is 01
• Day: =MID(A1,9,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,12,4), MONTH(DATEVALUE(MID(A1,5,3)&"1")), MID(A1,9,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 9: Text date "Thu/Jan/25/2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,12,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,5,3)&"1")), which is 01
• Day: =MID(A1,9,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,12,4), MONTH(DATEVALUE(MID(A1,5,3)&"1")), MID(A1,9,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 10: Text date "Thu Jan 25 2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,12,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,5,3)&"1")), which is 01
• Day: =MID(A1,9,2), which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,12,4), MONTH(DATEVALUE(MID(A1,5,3)&"1")), MID(A1,9,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Group 7: Full month name and 4 digits year

The text contains a full month name and the 4 digits year. The year, the month, the day and the weekday are in different orders with different separators including spaces.

Example 1: Text date "January 25, 2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,FIND(" ",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND(" ",A1)+1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(LEFT(A1,FIND(" ",A1)-1)&"1")), MID(A1,FIND(" ",A1)+1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 2: Text date "January.25.2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,FIND(".",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND(".",A1)+1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(LEFT(A1,FIND(".",A1)-1)&"1")), MID(A1,FIND(".",A1)+1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 3: Text date "January-25-2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,FIND("-",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND("-",A1)+1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(LEFT(A1, FIND("-",A1)-1)&"1")), MID(A1,FIND("-",A1)+1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 4: Text date "January/25/2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,FIND("/",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND("/",A1)+1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(LEFT(A1,FIND("/",A1)-1)&"1")), MID(A1,FIND("/",A1)+1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 5: Text date "January 25 2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,FIND(" ",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND(" ",A1)+1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(LEFT(A1, FIND(" ",A1)-1)&"1")), MID(A1,FIND(" ",A1)+1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 6: Text date "January252015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,LEN(A1)-6)&"1")), which is 01
• Day: =MID(A1,LEN(A1)-5,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(DATEVALUE(LEFT(A1,LEN(A1)-6)&"1")), MID(A1,LEN(A1)-5,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 7: Text date "2015.January.25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,6,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)&"1")), which is 01
• Day: =RIGHT(A1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1,6,FIND(".", A1,FIND(".",A1)+1)-FIND(".",A1)-1)&"1")), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 8: Text date "2015-January-25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,6,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1)&"1")), which is 01
• Day: =RIGHT(A1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1,6, FIND("-",A1,FIND("-",A1)+1) – FIND("-",A1)-1)&"1")), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 9: Text date "2015/January/25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,6,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&"1")), which is 01
• Day: =RIGHT(A1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1,6,FIND("/",A1, FIND("/",A1)+1) – FIND("/",A1)-1)&"1")), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 10: Text date "2015 January 25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,6,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)&"1")), which is 01
• Day: =RIGHT(A1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1,6, FIND(" ",A1,FIND(" ",A1)+1) – FIND(" ",A1)-1)&"1")), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Group 8: Full month name, 4 digits year and time

The text contains a full month name and the 4 digits year. The year, the month, the day and the weekday are in different orders with different separators including spaces.

Example 1: Text date "January 25, 2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND(",",A1)+2,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,FIND(" ",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND(" ",A1)+1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,FIND(",",A1)+2,4), MONTH(DATEVALUE(LEFT(A1, FIND(" ",A1)-1)&"1")), MID(A1, FIND(" ",A1)+1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 2: Text date "January.25.2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,FIND(".",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND(".",A1)+1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1, FIND(".",A1,FIND(".",A1)+1)+1,4), MONTH(DATEVALUE(LEFT(A1, FIND(".",A1)-1)&"1")), MID(A1, FIND(".",A1)+1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 3: Text date "January-24-2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND("-",A1,FIND("-",A1)+1)+1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,FIND("-",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND("-",A1)+1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1, FIND("-",A1, FIND("-",A1)+1)+1,4), MONTH(DATEVALUE(LEFT(A1, FIND("-",A1)-1)&"1")), MID(A1, FIND("-",A1)+1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 4: Text date "January/25/2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND("/",A1,FIND("/",A1)+1)+1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,FIND("/",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND("/",A1)+1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1, IND("/",A1, FIND("/",A1)+1)+1,4), MONTH(DATEVALUE(LEFT(A1, FIND("/",A1)-1)&"1")), MID(A1, FIND("/",A1)+1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 5: Text date "January 25 2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,4), which is 2015
• Month: =MONTH(DATEVALUE(LEFT(A1,FIND(" ",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND(" ",A1)+1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1, FIND(" ",A1, FIND(" ",A1)+1)+1,4), MONTH(DATEVALUE(LEFT(A1, FIND(" ",A1)-1)&"1")), MID(A1, FIND(" ",A1)+1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 6: Text date "2015January25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,5,FIND(" ",A1)-3-4)&"1")), which is 01
• Day: =MID(A1,FIND(" ",A1)+1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1,5, FIND(" ",A1)-3-4)&"1")), MID(A1, FIND(" ",A1)-2,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 7: Text date "2015.January.25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND(" ",A1)-2,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1, FIND(".",A1)+1, FIND(".",A1, FIND(".",A1)+1) – FIND(".",A1)-1)&"1")), MID(A1, FIND(" ",A1)-2,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 8: Text date "2015-January-25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND(" ",A1)-2,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1, FIND("-",A1)+1, FIND("-",A1, FIND("-",A1)+1) – FIND("-",A1)-1)&"1")), MID(A1, FIND(" ",A1)-2,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 9: Text date "2015/January/25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,FIND("/",A1)+1, FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND(" ",A1)-2,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1, FIND("/",A1)+1, FIND("/",A1, FIND("/",A1)+1)- FIND("/",A1)-1)&"1")), MID(A1, FIND(" ",A1)-2,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 10: Text date "2015 January 25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =LEFT(A1,4), which is 2015
• Month: =MONTH(DATEVALUE(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)&"1")), which is 01
• Day: =MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(LEFT(A1,4), MONTH(DATEVALUE(MID(A1, FIND(" ",A1)+1, FIND(" ",A1, FIND(" ",A1)+1) – FIND(" ",A1)-1)&"1")), MID(A1,FIND(" ",A1, FIND(" ",A1)+1)+1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Group 9: Full month and weekday name

The text contains a full month and weekday names. The year, the month, the day and the weekday are in different orders with different separators including spaces.

Example 1: Text date "Thursday2015January25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND("day",A1)+3,4), which is 2015
• Month: =MONTH(MID(A1,FIND("day",A1)+7,LEN(A1)-2-find("day",A1)-6)&"1"), which is 01
• Day: =RIGHT(A1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1, FIND("day",A1)+3,4), MONTH(MID(A1, FIND("day",A1)+7, LEN(A1)-2- FIND("day",A1)-6)&"1"), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 2: Text date "Thursday.2015.January.25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND(".",A1)+1,4), which is 2015
• Month: =MONTH(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1, FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-FIND(".",A1, FIND(".",A1)+1)-1)&"1"), which is 01
• Day: =RIGHT(A1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1, FIND(".",A1)+1,4), MONTH(MID(A1, FIND(".",A1, FIND(".",A1)+1)+1, FIND(".",A1,FIND(".",A1, FIND(".",A1)+1)+1)-FIND(".",A1, FIND(".",A1)+1)-1)&"1"), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 3: Text date "Thursday-2015-January-25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND("-",A1)+1,4), which is 2015
• Month: =MONTH(MID(A1,FIND("-",A1, FIND("-",A1)+1)+1, FIND("-",A1,FIND("-",A1,FIND("-",A1)+1)+1) -FIND("-",A1, FIND("-",A1)+1)-1)&"1"), which is 01
• Day: =RIGHT(A1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,FIND("-",A1)+1,4), MONTH(MID(A1, FIND("-",A1, FIND("-",A1)+1)+1,FIND("-",A1, FIND("-",A1,FIND("-",A1)+1)+1)- FIND("-",A1, FIND("-",A1)+1)-1)&"1"), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 4: Text date "Thursday/2015/January/25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND("/",A1)+1,4), which is 2015
• Month: =MONTH(MID(A1,FIND("/",A1,FIND("/",A1)+1)+1,FIND("/",A1,FIND("/",A1,FIND("/",A1)+1)+1)-FIND("/",A1,FIND("/",A1)+1)-1)&"1"), which is 01
• Day: =RIGHT(A1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,FIND("/",A1)+1,4), MONTH(MID(A1, FIND("/",A1, FIND("/",A1)+1)+1, FIND("/",A1, FIND("/",A1,FIND("/",A1)+1)+1)-FIND("/",A1, FIND("/",A1)+1)-1)&"1"), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 5: Text date "Thursday 2015 January 25" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND(" ",A1)+1,4), which is 2015
• Month: =MONTH(MID(A1,FIND(" ",A1, FIND(" ",A1)+1)+1, FIND(" ",A1, FIND(" ",A1, FIND(" ",A1)+1)+1) – FIND(" ",A1, FIND(" ",A1)+1)-1)&"1"), which is 01
• Day: =RIGHT(A1,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,FIND(" ",A1)+1,4), MONTH(MID(A1, FIND(" ",A1, FIND(" ",A1)+1)+1, FIND(" ",A1, FIND(" ",A1, FIND(" ",A1)+1)+1)- FIND(" ",A1, FIND(" ",A1)+1)-1)&"1"), RIGHT(A1,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 6: Text date "ThursdayJanuary252015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(MID(A1, FIND("day",A1)+3, LEN(A1)-6- FIND("day",A1)-2)&"1"), which is 01
• Day: =MID(A1,LEN(A1)-5,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(MID(A1, FIND("day",A1)+3, LEN(A1)-6- FIND("day",A1)-2)&"1"), MID(A1,LEN(A1)-5,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 7: Text date "Thursday.January.25.2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(MID(A1, FIND(".",A1)+1, FIND(".",A1, FIND(".",A1)+1)- FIND(".",A1)-1)&"1"), which is 01
• Day: =MID(A1,LEN(A1)-6,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(MID(A1, FIND(".",A1)+1, FIND(".",A1, FIND(".",A1)+1)- FIND(".",A1)-1)&"1"), MID(A1,LEN(A1)-6,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 8: Text date "Thursday-January-25-2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1)&"1"), which is 01
• Day: =MID(A1,LEN(A1)-6,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(MID(A1,FIND("-",A1)+1, FIND("-",A1,FIND("-",A1)+1)- FIND("-",A1)-1)&"1"), MID(A1,LEN(A1)-6,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 9: Text date "Thursday/January/25/2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(MID(A1, FIND("/",A1)+1, FIND("/",A1, FIND("/",A1)+1)- FIND("/",A1)-1)&"1"), which is 01
• Day: =MID(A1, LEN(A1)-6,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4), MONTH(MID(A1, FIND("/",A1)+1, FIND("/",A1, FIND("/",A1)+1) – FIND("/",A1)-1)&"1"), MID(A1,LEN(A1)-6,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 10: Text date "Thursday January 25 2015" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =RIGHT(A1,4), which is 2015
• Month: =MONTH(MID(A1,FIND(" ",A1)+1, FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)&"1"), which is 01
• Day: =MID(A1,LEN(A1)-6,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(RIGHT(A1,4),MONTH(MID(A1, FIND(" ",A1)+1, FIND(" ",A1,FIND(" ",A1)+1) – FIND(" ",A1)-1)&"1"), MID(A1,LEN(A1)-6,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Group 10: Full month weekday name and time

The text contains a full month and weekday names, plus time. The year, the month, the day and the weekday are in different orders with different separators including spaces.

Example 1: Text date "Thursday2015January25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND("day",A1)+3,4), which is 2015
• Month: =MONTH(MID(A1,FIND("day",A1)+7, FIND(" ",A1)-3-FIND("day",A1)-6)&"1"), which is 01
• Day: =MID(A1,FIND(" ",A1)-2,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1, FIND("day",A1)+3,4), MONTH(MID(A1, FIND("day",A1)+7, FIND(" ",A1)-3- FIND("day",A1)-6)&"1"), MID(A1, FIND(" ",A1)-2,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 2: Text date "Thursday.2015.January.25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND(".",A1)+1,4), which is 2015
• Month: =MONTH(MID(A1, FIND(".",A1, FIND(".",A1)+1)+1, FIND(".",A1, FIND(".",A1,FIND(".",A1)+1)+1)- FIND(".",A1,FIND(".",A1)+1)-1)&" 1"), which is 01
• Day: =MID(A1,FIND(" ",A1)-2,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1, FIND(".",A1)+1,4), MONTH(MID(A1, FIND(".",A1, FIND(".",A1)+1)+1, FIND(".",A1, FIND(".",A1,FIND(".",A1)+1)+1)- FIND(".",A1, FIND(".",A1)+1)-1)&"1"), MID(A1,FIND(" ",A1)-2,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 3: Text date "Thursday-2015-January-25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND("-",A1)+1,4), which is 2015
• Month: =MONTH(MID(A1, FIND("-",A1, FIND("-",A1)+1)+1, FIND("-",A1,FIND("-",A1, FIND("-",A1)+1)+1)-FIND("-",A1,FIND("-",A1)+1)-1)&"1"), which is 01
• Day: =MID(A1,FIND(" ",A1)-2,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,FIND("-",A1)+1,4), MONTH(MID(A1, FIND("-",A1,FIND("-",A1)+1)+1, FIND("-",A1, FIND("-",A1, FIND("-",A1)+1)+1)- FIND("-",A1, FIND("-",A1)+1)-1)&"1"), MID(A1, FIND(" ",A1)-2,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 4: Text date "Thursday/2015/January/25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND("/",A1)+1,4), which is 2015
• Month: =MONTH(MID(A1, FIND("/",A1, FIND("/",A1)+1)+1, FIND("/",A1, FIND("/",A1, FIND("/",A1)+1)+1) – FIND("/",A1, FIND("/",A1)+1)-1)&"1"), which is 01
• Day: =MID(A1,FIND(" ",A1)-2,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,FIND("/",A1)+1,4), MONTH(MID(A1, FIND("/",A1, FIND("/",A1)+1)+1, FIND("/",A1, FIND("/",A1, FIND("/",A1)+1)+1)- FIND("/",A1, FIND("/",A1)+1)-1)&"1"), MID(A1, FIND(" ",A1)-2,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 5: Text date "Thursday 2015 January 25 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND(" ",A1)+1,4), which is 2015
• Month: =MONTH(MID(A1, FIND(" ",A1, FIND(" ",A1)+1)+1, FIND(" ",A1, FIND(" ",A1, FIND(" ",A1)+1)+1)- FIND(" ",A1, FIND(" ",A1)+1)-1)&"1"), which is 01
• Day: =MID(A1, FIND(":",A1)-4,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,FIND(" ",A1)+1,4), MONTH(MID(A1, FIND(" ",A1, FIND(" ",A1)+1)+1, FIND(" ",A1,FIND(" ",A1, FIND(" ",A1)+1)+1)- FIND(" ",A1, FIND(" ",A1)+1)-1)&"1"), MID(A1,FIND(":",A1)-4,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 6: Text date "ThursdayJanuary252015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND(" ",A1)-4,4), which is 2015
• Month: =MONTH(MID(A1, FIND("day",A1)+3, FIND(" ",A1)-6- FIND("day",A1)-3)&"1"), which is 01
• Day: =MID(A1,FIND(" ",A1)-6,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,FIND(" ",A1)-4,4), MONTH(MID(A1, FIND("day",A1)+3, FIND(" ",A1)-6- FIND("day",A1)-3)&"1"), MID(A1,FIND(" ",A1)-6,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 7: Text date "Thursday.January.25.2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND(" ",A1)-4,4), which is 2015
• Month: =MONTH(MID(A1,FIND(".",A1)+1, FIND(".",A1, FIND(".",A1)+1)- FIND(".",A1)-1)&"1"), which is 01
• Day: =MID(A1,FIND(" ",A1)-7,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,FIND(" ",A1)-4,4), MONTH(MID(A1,FIND(".",A1)+1, FIND(".",A1, FIND(".",A1)+1)- FIND(".",A1)-1)&"1"), MID(A1,FIND(" ",A1)-7,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 8: Text date "Thursday-January-25-2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND(" ",A1)-4,4), which is 2015
• Month: =MONTH(MID(A1,FIND("-",A1)+1, FIND("-",A1, FIND("-",A1)+1)- FIND("-",A1)-1)&"1"), which is 01
• Day: =MID(A1,FIND(" ",A1)-7,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1, FIND(" ",A1)-4,4), MONTH(MID(A1, FIND("-",A1)+1, FIND("-",A1, FIND("-",A1)+1)- FIND("-",A1)-1)&"1"), MID(A1, FIND(" ",A1)-7,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 9: Text date "Thursday/January/25/2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND(" ",A1)-4,4), which is 2015
• Month: =MONTH(MID(A1,FIND("/",A1)+1, FIND("/",A1, FIND("/",A1)+1)- FIND("/",A1)-1)&"1"), which is 01
• Day: =MID(A1,FIND(" ",A1)-7,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,FIND(" ",A1)-4,4), MONTH(MID(A1, FIND("/",A1)+1, FIND("/",A1, FIND("/",A1)+1)- FIND("/",A1)-1)&"1"), MID(A1,FIND(" ",A1)-7,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.

Example 10: Text date "Thursday January 25 2015 1:30:20" in cell A1

Step 1: To first extract the year, the month and the day:

• Year: =MID(A1,FIND(":",A1)-6,4), which is 2015
• Month: =MONTH(MID(A1, FIND(" ",A1)+1, FIND(" ",A1, FIND(" ",A1)+1)- FIND(" ",A1)-1)&"1"), which is 01
• Day: =MID(A1,FIND(":",A1)-9,2) which is 25

Step 2: To merge the year, the month and the day together using the DATE function:

=DATE(MID(A1,FIND(":",A1)-6,4), MONTH(MID(A1, FIND(" ",A1)+1, FIND(" ",A1, FIND(" ",A1)+1)- FIND(" ",A1)-1)&"1"), MID(A1,FIND(":",A1)-9,2))

Step 3: Change cell A1 in the formula and change the date format using the "formbackgroundat cells" method.