DATE Formula in Excel

The DATE formula in Excel returns the serial number of a date based on year, month and day. The DATE formula in Excel is a part of Date & Time formulas.

The Excel DATE formula returns a valid date based on the year, month and day as arguments. Each of the arguments are provided as number and result a valid date in Excel. You can define all the components of a date and the formula will return the date as output. The DATE formula in Excel is useful to combine the dates dynamically based on the values in different cells. The date is shown in Excel based on your system setting. You can use the format cell feature in Excel to change the date format.

How to use the DATE Formula in Excel?

The article in this webpage describes how to use the DATE formula in Excel with syntax, detailed explanation and examples.

DATE Syntax

=DATE(year,month,day)

The arguments for DATE formula are:

Year (required argument)

• The value of the year can be from 1 to 4 digits. Always use the year in 4 digits to avoid unwanted results and confusion, because sometimes the year in 2 digits might be confusing (example: the year “05” might be “1905” or “2005”).
• In Microsoft Excel, the default first date is January 1, 1900.
• If the year is between 0 and 1899 then it returns the year by adding 1900. Example: =DATE(105,12,1) will return as December 1, 2005 (year 2005 = 105 + 1900).
• If the year is between 1900 and 9999 then it returns the same as year. Example: =DATE(2050,12,1) will return as December 1, 2050.
• If the year is less than 0, greater than or equal to 10000 then it returns as #NUM! error. Example: =DATE(20000,12,1) or =DATE(-100,12,1) will return as #NUM!.

Month (required argument)

• The value of the month can be from 1 to 12 (positive or negative).
• If the month is less than 1 then it subtracts the same number of months from the date. Example: =DATE(2050,-4,1) will return as August 1, 2049.
• If the month is greater than 12 then it adds the extra number of months to the date. Example: =DATE(2050,15,1) will return as March 1, 2051.

Day (required argument)

• The value of the day can be from 1 to 31 (positive or negative).
• If the day is less than the number of days in a specified month then it subtracts the same number of days from the date. Example: =DATE(2050,5,-10) will return as April 20, 2050.
• If the day is more than the number of days in a specified month then it adds the same number of days to the date. Example: =DATE(2050,5,35) will return as June 4, 2050.

DATE Examples

Example 1 (simple DATE formula)

In the below example of DATE formula in Excel, the date in column F6 is derived from the Year, Month and Day in cell B6, C6 and D6, respctively.

=DATE(B6,C6,D6)

Instead of cell reference, you can also define the year, month and day in the DATE formula as show below and it will give the same date as 05 April 2020.

=DATE(2020,4,5)

Example 2 (Increase or Decrease)

In the below DATE formula example, the date in column F6 is derived by adding 1 year, 2 month and deducting 10 days from the information about date in cell B6, C6 and D6.

=DATE(B6+1,C6+2,D6-10)

Example 3 (Dynamic)

In the below DATE formula example, the date in cell F6  (15 May 2020) is derived by considering the year from cell D5 (05 April 2020), the month from cell C6 (10 May 2025) and the day from cell C7 (15 Jun 2023).

=DATE(YEAR(C5), MONTH(C6), DAY(C7))

Important Notes

You must remeber the below key points while using the DATE formula in Excel:

• In Excel the default first date is January 1, 1900.
• The dates are stored in sequential serial numbers for calculation. Example: January 1, 1900 is serial number 1 and January 1, 2050 is 54,798 (54,797 days from January 1, 1900).
• The DATE formula will return #NUM! error if the given year argument is < 0 or ≥ 10000.
• The DATE formula will return #VALUE! error if any of the given argument is non-numeric.
• If the DATE formula returns a number then format the cell to get the desired date format.