I had a question on another post on how to convert Nov 21, 2014 into a date Excel recognises. The solution involves six functions working together.

The blog post where the question was posed explained how to convert a text month name like July  into a month number 7 – read it here.

Just a note – I use the dd/mm/yyyy date format as I am based in Australia.

The tricky bit with Nov 21, 2014 is the day number in the middle of the date structure. The month and the year are straightforward as their length is consistent. The day could be one or two digits.

The solution formula is shown below, it uses the technique from the other blog post for the month.

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

The DATE function takes three arguments, year, month and day in that order.

Lets examine the three arguments separately

RIGHT(A1,4)

The RIGHT function extracts text characters from the right of date – 4 characters, in our case for the year.

Even though the RIGHT function returns text, the DATE function will convert it into a real number to use as the year.

MONTH(1&LEFT(A1,3))

This is the part that came from the other blog post.

The technique adds a 1 to the start of the three character month name. Excel recognises this as a date and we can use it to get the month number with the MONTH function which returns a number from 1 to 12.

MID(A1,5,SEARCH(",",A1)-5)

This part returns the day number. This is the tricky part, as it could be one, or two, characters long.

The MID function extracts text from the middle of a text string.

It takes three arguments. You need to give it the cell to work with, the starting character position as a number and the number of characters to extract.

The starting character position will be included in the extraction.

We know the starting position of the first character is 5. We don’t know whether to extract one or two characters.

Luckily the SEARCH function can be used to return the character position of the comma that follows the day number.

The SEARCH function takes two arguments, what you are searching for, usually enclosed in quotation marks and the cell to search in.

We subtract 5 from this SEARCH result to calculate the number of characters to extract.

It is common to use these text functions together to flexibly extract text.