Microsoft Office Tutorials and References
In Depth Information
Working with Dates and Times
Using pre-1900 dates
The world, of course, didn’t begin on January 1, 1900. People who work with historical
information when using Excel often need to work with dates before January 1, 1900. Unfortunately, the
only way to work with pre-1900 dates is to enter the date into a cell as text. For example, you
can enter the following into a cell, and Excel won’t complain:
July 4, 1776
You can’t, however, perform any manipulation on dates that are actually text. For example, you
can’t change its formatting, you can’t determine which day of the week this date occurred on,
and you can’t calculate the date that occurs seven days later.
VBA, however, supports a much wider range of dates. I created a number of VBA worksheet functions
that allow you to work with pre-1900 dates. Figure 3-9 shows a demonstration of these functions
used in a worksheet. It’s also an excellent example of how VBA can extend the features in Excel.
Figure 3-9: The Extended Date Functions add-in lets you work with pre-1900 dates.
See Chapter 10 for more information about the Extended Date functions.