Microsoft Office Tutorials and References

In Depth Information

**Syntax**

Syntax

=DATE(year,month,day)

The DATE function returns the serial number that represents a particular

date. This function takes the following arguments:

•
year

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-

ror.

•
month

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

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

=DATE(Year(A2),Month(A2),1).

•
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.