Why your Excel macros don't always do what you expect when it comes to handling dates.

Casebook Index

Converting date formats when the date isn't a date!

I work in England. Here in the UK dates can be a bit of a problem for developers. This is because whilst we (and most of our fellow Europeans) are all used to thinking about dates in terms of Day/Month/Year our friends in the United States use the format Month/Day/Year.

When Opposites Collide

If I write "5 August 2001" and my US colleague writes "August 5 2001" we both know what they mean, even if we don't know who wrote it. But what if we see "5/8/2001"... is it 5 August or 8 May?

When working in your office programs, like Excel and Access, you shouldn't normally encounter any problems entering dates - as long as you do what the computer expects. These programs take their date settings from the Regional Settings control in the Windows Control Panel. Excel knows that my regional settings are English (United Kingdom) so if I enter 27/09/2001 into a cell it recognises my entry as 27 September 2001 and treats it as a date. If I enter 09/27/2001 into a cell Excel knows it can't be a date (as there are not 27 months) and so treats the entry as text. As long as I know that too and enter my dates properly we don't have a problem.

So we have rules - but those rules aren't so simple when it comes to working with programming languages like VBA (your macro language) and SQL (the database query language). These languages use the US date format. Often your VBA code needs to interact with your worksheet - and here is where it starts to get confusing.

5 Minute Primer in VBA Date Handling

To find out how VBA handles dates try this short exercise. Open Excel and enter the date 05/08/2001 in a cell. Press [Enter] then select the cell again. For me that date means 5 August 2001.

Start the Visual Basic Editor (keys: ALT+F11) and open the Immediate Window (keys: CTRL+G). The Immediate window is a sort of "scratchpad" that you can use to try out snippets of code, either on their own or interacting with Excel.

[If you live in the US you might like to switch your regional setting to English (United Kingdom) to see what I'm getting at. Alternatively leave them as they are and notice the difference between what you get and what I describe here.]

In the immediate window type ? ActiveCell.Value and press [Enter]. The immediate window shows:

? ActiveCell.Value
05/08/2001

I get what I typed in the cell - but does VBA interpret those numbers and slashes the same way as I do? To find out, type ? Month(ActiveCell) and press [Enter]...

? Month(Activecell)
8 

So VBA is interpreting the date as August, the same as the worksheet. Let's double-check. Type ? DateValue(ActiveCell) and press [Enter]...

? DateValue(ActiveCell)
05/08/2001

It seems that, when VBA is dealing with a date on a worksheet is uses the same date system as the worksheet. But what about inside the VBA itself. Type ? Month(#05/08/2001#) and press [Enter]...

? Month(#05/08/2001#)
5 

Now we get May! When working on its own, VBA reverts to the US date system. Try a different date. Type ? Month(#09/27/2001#) and press [Enter]...

? Month(#09/27/2001#)
9

As you expected, September. So just to confuse things, how about this... Type ? Month(#27/09/2001#) and press [Enter]...

? Month(#27/09/2001#)
9

Aaaaarrrgghh! Just when we thought we had it figured out it changes its mind! What's going on. How can it be month/day/year one minute and day/month/year the next?

Here's the unofficial (i.e. my) explanation. If your VBA code reads a date on a worksheet, it interprets the date in the same way as the worksheet does. But if VBA reads a date inside the code it assumes that it is written in month/day/year (i.e. US) date format. UNLESS (and there has to be a big UNLESS!) the date it reads is impossible. If it gets a date that doesn't conform to month/day/year it tries day/month/year, and if that doesn't work it tries year/month/day. If either of these work they get used.

Although few people use it, there is an International standard date format (ISO8601) which is ccyy/mm/dd (the cc stands for "century", representing the digits used in the thousands and hundreds components, as opposed to the actual century). Using this format my September date would be written 2001/09/27.

If I use that in my VBA it knows exactly what I mean. So does Excel. Try typing it into a cell. When you press [Enter] Excel changes it to your default date format but it interprets the date properly.

Perhaps one day we'll all use the ISO format. Meanwhile, problems sometimes arise and we have to deal with them. Here's one I came across recently...

Turning a Date That Isn't a Date Into a Date

A client of mine was using an Excel application I had written that collected and collated various kinds of data (including dates) from imported worksheets.

The application presented the user with a VBA generated User Form on which the user specified the ranges of cells to be collated. The VBA code read the data in the cells and wrote it on to another sheet having done all sorts of calculations and other operations. A second operation prepared the collated data for export as a .csv file, and a final operation exported it to a database. It all worked fine. Until...

One day I got a call from my client saying that dates were getting entered incorrectly. He had received some data that was in a format that he could simply copy and paste into the collation sheet without using the User Form. There didn't seem any reason why he shouldn't do this. The dates would go into the date column etc. etc. But when the data was prepared for export something went wrong. Days and months got switched around. Everything looked OK before export but there were columns of dates in the .csv file that read 01/01/2001, 01/02/2001, 01/03/2001 etc. when they should have read 01/01/2001, 02/01/2001, 03/01/2001 etc.

He had already collected a lot of data like this, so it would not have been helpful for me to say "You should have done it properly!" I needed to find a quick and easy way to turn all those dates round.

My solution was this little macro that could be run on each of the "faulty" worksheets...

Sub ConvertDate()
   Dim Cell As Object
   For Each Cell In Selection
      If IsDate(Cell.Value) Then
         Cell.Value = DateValue(Cell.Value)
      End If
    Next Cell
End Sub

The macro uses a simple For Each... Next... loop to examine each cell in a selection (the idea being that the user selects a column of cells and runs the macro).

An IF statement checks to see if the cell contains something that can be interpreted as a valid date. The user will probably want to select an entire column, which would include a heading cell containing text. Without the IF statement this cell would generate an error when the macro tried to convert it to a date.

If the cell contains a valid date it is changed to the correct format.

Is There a Lesson To Be Learnt Here?

This macro is highly specific to the job in hand. It isn't a universal date-fixing macro! But it does illustrate the fact that the code-writing part of the job is often the easy bit. The difficult part is finding out what has gone wrong and (the hardest bit) why.

The reason the dates came through incorrectly was that the VBA didn't get its hands on them before they were exported. If it had, it would have known what to do with them. Instead it got some data that it assumed had been through the system and it did what it normally did.

The lesson? No matter how much you plan and test your applications, the user will do something you hadn't anticipated. Then you fix it... and wait for the next call.