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

How to Convert Text to Date Format (100 examples)

Excel Formulas No Comments

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)

0125201520150125
01.25.20152015.01.25
01-25-20152015-01-25
01/25/20152015/01/25
01 25 20152015 01 25

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

012515150125
01.25.1515.01.25
01-25-1515-01-25
01/25/1515/01/25
01 25 1515 01 25 

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

Jan25201525Jan2015
Jan.25.201525.Jan.2015
Jan-25-201525-Jan-2015
Jan/25/201525/Jan/2015
Jan 25, 201525 Jan, 2015 

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

2015Jan25 1:30:20Jan252015 1:30:20
2015.Jan.25 1:30:20Jan.25.2015 1:30:20
2015-Jan-25 1:30:20Jan-25-2015 1:30:20
2015/Jan/25 1:30:20Jan/25/2015 1:30:20
2015 Jan 25 1:30:20Jan 25 2015 1:30:20

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

Thu2015Jan25ThuJan252015
Thu.2015.Jan.25Thu.Jan.25.2015
Thu-2015-Jan-25Thu-Jan-25-2015
Thu/2015/Jan/25Thu/Jan/25/2015
Thu 2015 Jan 25Thu Jan 25 2015

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

Thu2015Jan25 1:30:20ThuJan252015 1:30:20
Thu.2015.Jan.25 1:30:20Thu.Jan.25.2015 1:30:20
Thu-2015-Jan-25 1:30:20Thu-Jan-25-2015 1:30:20
Thu/2015/Jan/25 1:30:20Thu/Jan/25/2015 1:30:20
Thu 2015 Jan 25 1:30:20Thu Jan 25 2015 1:30:20

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

January 25, 2015January252015
January.25.20152015.January.25
January-25-20152015-January-25
January/25/20152015/January/25
January 25 20152015 January 25

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

January 25, 2015 1:30:202015January25 1:30:20
January.25.2015 1:30:202015.January.25 1:30:20
January-25-2015 1:30:202015-January-25 1:30:20
January/25/2015 1:30:202015/January/25 1:30:20
January 25 2015 1:30:202015 January 25 1:30:20

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

Thursday2015January25ThursdayJanuary252015
Thursday.2015.January.25Thursday.January.25.2015
Thursday-2015-January-25Thursday-January-25-2015
Thursday/2015/January/25Thursday/January/25/2015
Thursday 2015 January 25Thursday January 25 2015

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

Thursday2015January25 1:30:20ThursdayJanuary252015 1:30:20
Thursday.2015.January.25 1:30:20Thursday.January.25.2015 1:30:20
Thursday-2015-January-25 1:30:20Thursday-January-25-2015 1:30:20
Thursday/2015/January/25 1:30:20Thursday/January/25/2015 1:30:20
Thursday 2015 January 25 1:30:20Thursday January 25 2015 1:30:20

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.

Leave a Reply