How to turn “1st January, 2017” into #Excel recognizable date?

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:

Excel Tips - Date format from text to number.PNG

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. 🙂

Advertisements
This entry was posted in Formula and tagged , , , , , , . Bookmark the permalink.

5 Responses to How to turn “1st January, 2017” into #Excel recognizable date?

  1. aMareis says:

    How about this formula….

    =–REPLACE(A2,FIND(” “,A2)-2,2,””)

    Like

  2. Sabine says:

    Hello MF,
    you know I’m working with 2003 Professional and there is no Power Query for me. But I don’t need it because since version 2003 Office is able to speak SQL (stands for Structured Query Language). Try it with writing a serial letter. Normally you will get an infobox which tells you sth. about you are using a sql command now for connecting data source and letter. Even Outlook does understand this language and Access allthough.
    If a map requires a regular update then you can make a query (Data -> New Query). Make your settings and then you can actualize the sheet with two klicks. So you can import data very quickly.
    And you can do even more with Autofilter function because you can save your filtered list and place it prominently in your QAT for using it again. I’m using these features whenever I have to order new items for my office.

    Sabine

    Like

  3. Sabine says:

    Hello MF,
    it’s an interessting way to do it with formula. But, to be honest, I’ve only used ctrl + h for doing that. At first I’ve removed the commas and then th, rd and nd. At least I’ve replaced the c in December with a z (because that’s the correct spelling in Germany) and then removed st.
    I’ve even put a date 22nd August into my list. And everything worked well because Excel recognizes the date as a date directly after removing the nd.

    Sabine

    Like

    • MF says:

      Hi Sabine,
      You are right. If it’s only a one-off task, I would also do the Find and Replace… 🙂
      However, if it requires regular update… I would definitely go for Power Query that is discussed in the next post.
      Cheers,

      Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s