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)**

Jan252015 | 25Jan2015 |

Jan.25.2015 | 25.Jan.2015 |

Jan-25-2015 | 25-Jan-2015 |

Jan/25/2015 | 25/Jan/2015 |

Jan 25, 2015 | 25 Jan, 2015 |

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

2015Jan25 1:30:20 | Jan252015 1:30:20 |

2015.Jan.25 1:30:20 | Jan.25.2015 1:30:20 |

2015-Jan-25 1:30:20 | Jan-25-2015 1:30:20 |

2015/Jan/25 1:30:20 | Jan/25/2015 1:30:20 |

2015 Jan 25 1:30:20 | Jan 25 2015 1:30:20 |

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

Thu2015Jan25 | ThuJan252015 |

Thu.2015.Jan.25 | Thu.Jan.25.2015 |

Thu-2015-Jan-25 | Thu-Jan-25-2015 |

Thu/2015/Jan/25 | Thu/Jan/25/2015 |

Thu 2015 Jan 25 | Thu Jan 25 2015 |

**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)**

January 25, 2015 | January252015 |

January.25.2015 | 2015.January.25 |

January-25-2015 | 2015-January-25 |

January/25/2015 | 2015/January/25 |

January 25 2015 | 2015 January 25 |

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))

**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))