Microsoft Office Tutorials and References
In Depth Information
The DATE function returns the serial number that represents a particular
date. This function takes the following arguments:
year — This argument can be one to four digits. If yearis between 0
and 1899 (inclusive), Excel adds that value to 1900 to calculate the
year. For example, =DATE(100,1,2) returns January 2, 2000 (1900+100).
If year is between 1900 and 9999 (inclusive), Excel uses that value as
the year. For example, =DATE(2000,1,2) returns January 2, 2000. If
yearis less than 0 or is 10000 or greater, Excel returns a #NUM! er-
month — This is a number representing the month of the year. If month
is greater than 12, month adds that number of months to the first month
in the year specified. For example, =DATE(1998,14,2) returns the serial
number representing February 2, 1999.
day — This is a number representing the day of the month. If dayis
greater than the number of days in the month specified, it adds that
number of days to the first day in the month. For example,
=DATE(1998,1,35) returns the serial number representing February 4,
1998. In a trivial example, =DATE(2011,3,5) returns March 5, 2011.
The true power in the DATE function occurs when one or more of the year,
month, or day are calculated values. Here are some examples:
• If cell A2 contains an invoice date and you want to calculate the
day one month later, you use =DATE(Year(A2),Month(A2)+1,Day(A2)).
• To calculate the beginning of the month, you use
• To calculate the end of the month, you use
=DATE(Year(A2),Month(A2)+1,1) – 1.
The DATE function is amazing because it enables Excel to deal perfectly with
invalid dates. If your calculations for month cause it to exceed 12, this is no
problem. For example, if you ask Excel to calculate =DATE(2010,16,45), Ex-
cel considers the 16th month of 2010 to be April 2011. To find the 45th day of
April 2011, Excel moves ahead to May 15, 2011.
Figure 11.39 shows various results of the DATE and TIME functions.