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
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
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:
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]...
So VBA is interpreting the date as August, the same as the
worksheet. Let's double-check. Type ? DateValue(ActiveCell)
and press [Enter]...
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
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]...
As you expected, September. So just to confuse things, how
about this... Type ? Month(#27/09/2001#) and press [Enter]...
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
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
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.
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...
Dim Cell As Object
For Each Cell In Selection
If IsDate(Cell.Value) Then
Cell.Value = DateValue(Cell.Value)
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
If the cell contains a valid date it is changed to the correct
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
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
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.