Microsoft Office Tutorials and References
In Depth Information
Making a Date with DATE
4. Enter a comma ( ,).
5. Click the cell that has the number (1–12) that represents the month.
6. Enter a comma ( ,).
7. Click the cell that has the number (1–31) that represents the day of
8. Type a ), then press Enter.
Figure 12-3 displays a fourth column of dates that are created using DATE
and the values from the first three columns. The fourth column of dates has
been formatted so the dates are displayed in a standard format, not as a raw
date serial number.
a date from
DATE provides some extra flexibility with the month number. Negative
month numbers are subtracted from the specified year. For example, the
function =DATE(2014, -5, 15) returns the date July 15, 2013, because
July 2013 is 5 months before the first month of 2014. Numbers greater than
12 work the same way — =DATE(2014, 15, 1) returns March 1, 2015,
because March 2015 is 15 months after the first month of 2014.
Day numbers work the same way. Negative day numbers are subtracted
from the first of the specified month, and numbers that are greater than the
last day of the specified month wrap into later months. Thus, =DATE(2013,
2, 30) returns March 2, 2013, because February does not have 30 days.
Likewise, =DATE(2013, 2, 40) returns March 12, 2013.