Quite a long time ago, I wrote a post to discuss a trick to format date with “st”, “nd”, “rd”, etc…

In this post, I am going to discuss the reversed way: Turning an English written date with “Dst MMMM, YYYY” into date (i.e. number) that Excel recognizes.  The following screenshot illustrates the situation:

What’s the problem?

First of all, let’s understand what the problem we are facing here.  The date was presented as a real text string, not a number stored a text.  For instance, “21st December, 2017” is a real text string.  That means we cannot turn that text string with simple tricks discussed before.  Luckily, we are dealing with Excel problem that can be solved by formula.

Here’s the solution:

=(
IF(ISERROR(MID($A2,2,1)+0),LEFT($A2,1),LEFT($A2,2)) & "/" &
MID($A2,FIND(" ",$A2)+1,3) & "/" & 
RIGHT($A2,4)
)+0

How it works?

This formula looks complicated.  Indeed it is not.  Let’s dive into it.

1. Get the Day 
2. Get the Month
3. Get the Year
4. Concatenate the Day, Month and Year then turn it into number

Get the Day

IF(ISERROR(LEFT($A2,2)+0),LEFT($A2,1),LEFT($A2,2))

If the text is of a little better quality, i.e. always using 2 digits for day, the formula can be shorted to

LEFT($A2, 2)

Nonetheless, we understand that it’s a bit weird to see a date written as “01st December, 2017”.  No problem… We can fix that with a simple logical test.

We add 0 to the result of LEFT($A2, 2).  We will get an error when the day is of single digit.  For instance, LEFT($A2, 2) of “1st December, 2017” will yield a result of “1s”, which when added by 0 will give you an error as a result.

We could wrap the formula with ISERROR, and then put it in a simple IF statement:

IF(ISERROR(LEFT($A2,2)+0),TRUE,FALSE)
TRUE --> error --> The day is single-digit, LEFT(A2,1) is used
FALSE --> no error --> The day is double-digit, LEFT(A2, 2) is used

Make sense?

Note: If you are using Excel 2010 or later, you may use IFERROR for a shorter formula:

=IFERROR(LEFT($A2,2)+0,LEFT($A2,1))

Get the Month

=MID($A2,FIND(" ",$A2)+1,3)

This formula is quite straight forward.  FIND is used to identify the position of first space ” ” in the text string, which is the delimiter just before the month portion in the text string.  Then MID is deployed to get the month portion in the text string.

Note, we only need to get the first three letters of month as Excel can recognize short date with MMM format, and we don’t need to worry about the variable lengths for January to December.

Get the Year

RIGHT($A2,4)

This part gets the four letters from the right in the text string.  We use this to get the year portion.  No special trick here.

New to MID, LEFT, RIGHT functions?  You may refer to the post HERE to learn more.

What do we get so far?

Using “21st December, 2017” as an example, we get

1. A day with either 1 or 2 digit.  E.g. “21“
2. A three-letter month: E.g. “Dec“
3. A four-letter year: e..g “2017“

By concatenating these results with “/“, we get a text string “21/Dec/2017” as a result.  To turn this text string into an Excel recognizable date, we add 0 to it and format the result as Date.  As simple as this.

You may also want to learn more about Fixing Trouble Dates.

By putting all the short formula into one, we come up with the solution:

=(
IF(ISERROR(MID($A2,2,1)+0),LEFT($A2,1),LEFT($A2,2)) & "/" &
MID($A2,FIND(" ",$A2)+1,3) & "/" & 
RIGHT($A2,4)
)+0

Thinking that it is difficult to write long formula?  Take a look at the post – Writing a long formula in steps.

Not comfortable with formula?  No worry.  We can also fix this problem with Get and Transform, commonly know as Power Query.  Let’s talk about it in next post.  Stay tuned. 🙂