Excel DATE Function: Formulas and Examples

What is Excel DATE Function?

Excel Date function is a function under Excel Date and Time Function. It uses the individual year, month and day values as input arguments to get the date value. The input arguments can be entered using formulas or manually as the cell reference that contains the dates.

The DATE function is commonly used for financial and business analysis as financials are analysed based on specific time periods.

How Excel DATE Function Works?

The DATE function returns a serial number that represents a specific date in Excel. So the serial number 44197 represents the date 01 January 2021.

Syntax

The syntax for Excel DATE( ) function is:

=DATE(year, month, day)

Input Arguments:

Year – the year that is used in the date

Month – the month that is used in the date

Day – the day used for the date

Additional Notes

In Excel, dates are usually stored as serial numbers, but they can be displayed in various formats. The result displayed in the cell from the DATE function depends on the formatting of the cell. So, if the cell is set to General format, the result displayed is a series of numbers that represents the date. To get the result as a serial number, the cell format will be in General or Number.

It is recommended to always use the four-digit year to avoid getting unwanted results. Excel interprets the year argument based on the date system set on your computer. By default, Excel for windows uses the 1900 date system. So, if the year entered is less than four digits 1900, Excel will automatically add it to 1900 to make it a four-digit year. For example, if you enter ’20 as the Year value, Excel will make it 1920.

The Month value can take a negative or positive value of less than 0 or greater than 12. If the Month value is greater than 12, Excel automatically extends the month to the next year. Similarly, if the month value is less than 0, the Excel will take it back the numbers of the previous months.
The Day value can either be a positive or negative. If the value is negative, Excel deducts the number of days from the first day of the specified month. If it is more than the number of days in that month, Excel will add the extra days to the next month.

How to Use Excel DATE Function

Let’s take a look at some examples below.

Example 1: Using Code Numbers

In the above example, the DATE( ) function is used in the two scenarios and returns the same result, but are displayed differently. This is because the two cells are in different formats.

The result returned is a serial number when the cell is in ‘General’ format and returns a date when the cell is in ‘Date’ format.

Note that Excel stores a date or time as a number.

There are various date formats in Excel and they vary based on regional settings. For example, in the US, the date format used is DD-MM-YYYY while in the UK, the date format is MM-DD-YYYY.

Example 2: Using Cell Reference

You can also use the cell reference to get the date using the DATE function. This is very useful when you need to assemble the Year, Month and Day from the cells.

This is useful as it changes dynamically based on the inputs in the cells/worksheet.

See example shown below:

Here, the formula references the cells that has the year, month and day instead of using numbers.

Here, the formula references a combination of cells and code numbers.

Example 3: Using Negative Values for Months or Days

As explained earlier, the DATE function can also take negative values for the month and days. When you use a negative value for a month or day, let’s say -2, Excel will return the date by going 2 months back.

So for example, the formula =DATE(2020, -2, 1) will return the date October 1, 2019. Similarly, you can use any negative number in the months.