How to use the DATE function in Microsoft Excel

Let's look at some Excel Date function examples and explore how to use the Date function as a worksheet function in Microsoft Excel:

Summary

Excel Date and Time functions can be used to extract information from, and perform operations on, Excel Dates and Times.
The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.
The DATE function is a built-in function in Excel that is categorized as a Date/Time Function.

Purpose

Create a date with year, month, and day.

Return value

A valid Excel date.

What Does It Do ?

This function creates a real date by using three normal numbers typed into separate cells.

Syntax

=DATE (year, month, day)

Parameters or Arguments

Year: A number that is between one and four digits that represents the year.
Month: A number representing the month value.
Day: A number representing the day value.

Formatting

The result will normally be displayed in the m/d/yyyy format. By using the Format,Cells,Number,Date command the format can be changed.

Usage notes

The DATE function creates a valid Excel date using individual year, month, and day components. For example, you can use the DATE function to create the dates October 01, 2010

=DATE(2010,10,01) || Result: Oct/01/2010

The DATE function is useful for assembling dates that need to change dynamically based on other values. For example, 01 day in cell B5; May month in cell C5 and 2000 in cell D5:

=DATE(D5,C5,B5) || Result: May/01/2000

If D5 is then changed to 2010, the DATE function will return a date for May/01/2010 or
If C5 is then changed to Jun, the DATE function will return a date for Jun/01/2000 or
If B5 is then changed to 06, the DATE function will return a date for May/06/2000

Note: dates are serial numbers in Excel, and begin on January 1, 1900. Dates before 1900 are not supported. To display date values in a human-readable date format, apply a the number format of your choice.

Notes:

• Excel dates begin in the year 1900. If year is between zero and 1900, Excel will add 1900 to the year. If the year is between 1900 and 9999, the DATE function uses the year value as the year.
• If your cell is formatted as General and you enter the DATE function, Excel will format your result as m/d/yyyy based on your Regional Settings. If you wish to see the serial number result from the DATE function, you will have to change the format of the cell to General after entering the formula.
• #NUM! error – Occurs when the given year argument is < 0 or ≥ 10000.
• #VALUE! Error – Occurs if any of the given argument is non-numeric.
• Month can be greater than 12 and less than zero or month can be positive or negative. If month is greater than 12, Excel will add month to the first month in the specified year. If month is less than or equal to zero, Excel will subtract the absolute value of month plus 1 (ABS(month) + 1) from the first month of the given year.
• Day can be greater than 31* and less than zero or day can be positive or negative. If day is greater than the days in the given month, Excel will add day to the first day of the specified month. If day is less than or equal to zero, Excel will subtract the absolute value of day plus 1 (ABS(day) + 1) from the first day of the specified month.

Excel Date Function Examples

The following spreadsheet shows some simple examples of the Excel Date function:

Because the DATE function returns a serial date, we wanted to show you the result from the DATE function as an unformated serial date which you can see this in column E.

We also wanted to show you the result as a formatted date which is how a user would format the results. In column F, we have formatted the result from the DATE function as m/d/yyyy.

The above examples show that each argument to the Date function can be supplied as a single value or cell reference, or as an array of values or cells.

Click here to learn more excel function