Dealing With Unusual Date Formats

Posted on August 28, 2007

I’m sure that you have all been through this: You go to a Website (or a database, probably from a mainframe) to get some data. The problem is that the dates are formatted in some obscure way that Excel doesn’t understand. In this post, I’ll give you an idea of how to convert these dates into something that you can work with.

Yesterday I was working on a project that involves charting some economic data series. Frequently, charts of economic time series show shaded areas that indicate a period of recession (I’ve got a post that shows how to do that). So, I needed to know the dates for post-war recessions. Of course, the NBER is the ultimate source of this data and they happen to have a very nice page where you can get the data.

The problem is that the dates are in a somewhat odd format. For example, the most recent recession began in the first quarter of 2001 and ended in the third quarter of 2001. Those dates are shown as “March 2001 (I)” and “November 2001 (IV)”. These are perfectly readable by human beings (I am sometimes proud to claim membership in that group), but Excel doesn’t recognize them as dates.

I should note that Excel would recognize them as dates if it wasn’t for the quarter notation at the end of the string (e.g., the “ (IV)” part). So, if you type in “March 2001” Excel will convert that to a date (March 1, 2001). Also, realize that there are many odd date formats out there (e.g, 20010301 might indicate March 1, 2001), and this is only one.

These problems don’t have to be corrected by hand. Instead, you should use the various string functions (Excel calls them Text functions in the Insert Function dialog box) in combination with the date and time functions to parse the strings into something that Excel understands.

The useful string functions for our purposes here are:

* Find - As the name indicates, this function is used to find the first occurrence of a string inside of another string. It returns the position of the string, if it exists. This function is case-sensitive, so “A” is not the same as “a”.
* Search - Same as Find(), but it is not case-sensitive.
* Len - Returns the length of the string as the number of characters.
* Left - This function lets you extract the first N characters from a string.
* Mid - Similar to Left(), but you extract text from the middle of the string.
* Right - Again, similar to Left() and Mid(), but you extract text from the right side of the string.

There are other string functions, but these are the ones that you might use for this purpose.

Let’s take the “March 2001 (I)” string as our input. Assume that the string is located in cell A1. We could extract the month by using:

=Left(A1,5)

That tells Excel to grab the first 5 characters from the string located in cell A1. Similarly, we could get the year by using:

=Mid(A1,7,4)

which says to start at the 7th character and grab 4 characters.

If we put those functions into two different cells, then we would have a string (March) in the first cell, and a number (2001) in the second. Unfortunately, that doesn’t do what I want. Instead, I want Excel to recognize March 2001 as a date. To do this, I can combine the two formulas using concatenation:

=Left(A1,5)&” “&Mid(A1,7,4)

This will give me the string “March 2001”. Note that even though this is a string, Excel will recognize it as a date and you can do date math (add or subtract days, etc) with it.

Great, but in most cases we aren’t really done yet. I want to convert it to a particular day of the month. Excel assumes that March 2001 means March 1, 2001. In some cases that is fine, but in others you may need it to mean the 15th, the last day of the month, or whatever. In my case, I want the beginning of the recession to be the first day of the month (March 1, 2001) and the end of the recession to be the last day of the month (November 30, 2001). Furthermore, the formulas that I gave above work great for March, but they fail for every other month because I hard-coded the number of characters.

Let’s take the second problem first. I want to get rid of the hard-coded number of characters so that I can extract the name of any month. To do this, I’ll use the Find() function. Find() will be used to locate where the first space occurs. Using the example from above:

=Find(” “,A1)

will return 6. That indicates that the first space is the 6th character. (Note that I have left out the optional starting character argument. If you want to, you can start search at, say, the 10th character.) This tells me that the last character of the name of the month is the 5th character in the string. Now, to get the first N characters, I’ll do this:

=Left(A1,Find(” “,A1)-1)

That gives me the name of the month. To get the year, I need to use the Mid() function:

=MID(A1,FIND(” “,A1)+1,4)

This locates the first space, and then grabs the next 4 characters. Since the year, in this example, is always 4 characters I can hard-code the 4 without worry. The result is “2001”. I can now concatenate these results:

=Left(A1,Find(” “,A1)-1)&” “&MID(A1,FIND(” “,A1)+1,4)

which results in “March 2001” or “November 2001” depending on the date that is in A1.

At this point, my problem is to convert “November 2001” into the date “November 30, 2001”. To do this, I could use the DateValue() function to directly convert the string to a date, but that will give me “November 1, 2001” and I want the 30th. So, I will use the Date() function instead.

Now, Date() requires three arguments: Year, Month, Day. So, I need to supply each of those arguments. For example, I could do this:

=Date(2001,11,30)

Except that I would calculate those arguments by breaking the string apart using the text functions as described above. Unfortunately, there is yet another problem: Some months have 30 days, others have 31, and February might have either 28 or 29 days. Therefore, we can’t just set the Day argument to 30 as I did above.

Fortunately, Excel has a function called EOMonth() that will figure out the last day of any given month and year. I can use this function as follows:

=EOMonth(Date(2001,11,1),0)

So, I gave EOMonth() the first day of November 2001, and it figured out that the last day of the month is the 30th.

Ok, let’s wrap this up by combining all of the above. Suppose that cell A1 contains the string “November 2001 (IV)” and I want to convert that to a date. Specifically, I want the date to be the very last day of the month. The formula that I would use is:

=EOMONTH(DATEVALUE(LEFT(A1,FIND(” “,A1)-1)&MID(A1,FIND(” “,A1)+1,4)),0)

Note that I get away with using DateValue(), instead of Date(), because the EOMonth() function automatically figures the last day of the month for me. The formula can now be copied to other cells so that it can convert other dates automatically.

Presumably, you wouldn’t go to all of this trouble if you only have a couple of dates to convert. However, if you have many (maybe hundreds or thousands) then this combination of string and date functions can save you a lot of time and effort.

Comments:

Name:

Email (will not be displayed):

Location:

URL:

Smileys

Remember my personal information

Notify me of follow-up comments?

Submit the word you see below:

<< Back to main