 |
|
|
|
Excel and Dates
Dates are frequently used in Excel and in VBA for Excel, also for this reason I believe it is an important aspect to at least know the fundamentals of. The text below is from the Excel help file and explains how Excel sees or interprets Dates.
How Microsoft Excel performs date and time calculations
Microsoft Excel stores dates as sequential numbers known as serial values and stores times as decimal fractions because time is considered a portion of a day. Dates and times are values and therefore can be added, subtracted, and included in other calculations. For example, to determine the difference between two dates, you can subtract one date from the other. You can view a date or time as a serial number or a decimal fraction by changing the format of the cell that contains the date or time to General format.
Microsoft Excel supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Excel 97 for Windows is 1900. To change to the 1904 date system, click Options on the Tools menu, click the Calculation tab, and then select the 1904 date system check box.
Note: When you enter a date in Microsoft Excel 97, or later and you enter only two digits for the year, Microsoft Excel enters the year as follows:
The years 2000 through 2029 if you type 00 through 29 for the year. For example, if you type 5/28/19, Microsoft Excel assumes the date is May 28, 2019.
The years 1930 through 1999 if you type 30 through 99 for the year. For example, if you type 5/28/91, Microsoft Excel assumes the date is May 28, 1991.
End of Excel Help
Using Dates in VBA for Excel offers a lot more flexibility, but also has more pitfalls that can catch the uninformed out. This is due mainly to the fact that Excel is used globally and there is more than one Date system. We will look at the two most common and that is the American (Month-Day-Year) and the European (Day-Month-Year). This issue will be nearly non-existent if you know for a fact that the code written in VBA will only be used on one Date system. But should you write a Procedure that will be used by more than one Date system, problems can arise. In today's market it is not unusual for say an English company to have to deal with a Spreadsheet that uses the American Date System or an vice versa. You can imagine the problems that could arise if the Dates you insert into a Spreadsheet via VBA are assumed to be of the American Date System when in reality they are of the European Date System. Fortunately the makers of VBA for Excel have realised this and provided a universal Function to eliminate possible disasters. I would urge you to again form a good habit early and incorporate it whenever dealing with Dates. The Function is called the "DateSerial" Function. It has the Syntax:
Sub UniversalDate()
Dim dTheDate As Date
dTheDate = DateSerial(2001, 6, 5)
Range("A1").Value = dTheDate
End Sub
Using this Function will eliminate any possible confusion of the Date System used. Whenever you use or Parse a Date to a Variable or Range you must enclose it within the # (Hash signs) eg;
If you try and type this into Excel exactly as is you will see that Excel will automatically change it to the American Date System whether you want it to or not. In other words it will end up like:
dTheDate = #5/22/01#
You may or may not notice the Month and Day being switched if you are happily typing away. But worse than this is if you use:
Sub UniversalDate()
Dim dTheDate As Date
dTheDate = #10/12/01#
Range("A1").Value = dTheDate
End Sub
….and you are used to the European Date System you would assume the Date going into Range A1 is the 10th Day of December, 2001. Guess what,
WRONG! You will actually end up with the 12th Day of October, 2001 instead. Of course if we aware of this and we have formed the good habit of using the "DateSerial" Function for all Dates no such problem will arise. Eg;
Sub UniversalDate()
Dim dTheDate As Date
dTheDate = DateSerial(2001, 12, 10)
Range("A1").Value = dTheDate
End Sub
I hope this stresses the importance of using the DateSerial Function whenever you are working with Dates in Excel
Date Functions
Now that we realise the possible pitfalls of working with Dates I will show you some handy Functions you can use in Excel when working with Dates.
The first one is the "Date" Function, this will return the current systems Date. As it is returning the current system Date, it will use the Date System of the PC it is run on.
Next we have the "DateValue" Function. This is the same as the Worksheet Formula
DATEVALUE in that it will return a real Date from a String Date, eg;
Sub UniversalDate()
Dim dTheDate As Date
dTheDate = DateValue("12/May/01")
Range("A1").Value = dTheDate
End Sub
This will Parse the Date Value for 12th day of May, 2001 to our Date Variable "dTheDate", which in turn will place it in cell A1. You may also have noticed that we could also use the "DateValue" function to prevent mishaps when working with Dates. There is no reason why you couldn't and it won't matter so long as you use one or the other.
To add a specified period of time to a Date we could use the "DateAdd" Function, this has the Syntax:
Sub DateAddFunction()
Dim dTheDate As Date
dTheDate = DateAdd("d", 45, "18/may/2001")
Range("A1").Value = dTheDate
End Sub
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
There are many other types of Functions that can be used with Dates but as this is level 1 VBA I won't confuse the issue by going into them all. The two most important points to remember when working with Dates are How Microsoft Excel performs date and time calculations and being aware of the International issues associated with Dates.