Microsoft Office Tutorials and References
In Depth Information
In Chapter 26, I present some custom VBA functions that enable you to work with any
date in the years 0100 through 9999.
Inconsistent date entries
You need to be careful when entering dates by using two digits for the year. When you do so, Excel has some
rules that kick in to determine which century to use. And those rules vary, depending on the version of Excel
that you use.
Two-digit years between 00 and 29 are interpreted as 21st Century dates, and two-digit years between 30 and 99
are interpreted as 20th Century dates. For example, if you enter 12/15/28, Excel interprets your entry as Decem-
ber 15, 2028. However, if you enter 12/15/30, Excel sees it as December 15, 1930, because Windows uses a de-
fault boundary year of 2029. You can keep the default as is or change it by using the Windows Control Panel.
Display the Regional and Language Options dialog box. Then click the Customize button to display the Cus-
tomize Regional Options dialog box. Select the Date tab and then specify a different year.
The best way to avoid any surprises is to simply enter all years using all four digits for
Excel has quite a few functions that work with dates. They are all listed under the Date & Time drop-down list
in the Formulas ⇒ Function Library group.
Table 6-4 summarizes the date-related functions available in Excel.
Table 6-4: Date-Related Functions
Returns the serial number of a date given the year, month, and
Calculates the number of days, months, or years between two
Converts a date in the form of text to an actual date
Returns the day of the month for a given date
Returns the number of days between two dates
Calculates the number of days between two dates based on a