What is the Excel DATE Function?
The DATE function returns a date by specifying the numerical year, month and day.
The DATE function is the opposite of the DAY function, MONTH function and YEAR function that extracts the number of days, months and years from the date.
The DATE function is beneficial when you need a date as inputs to other functions, supply the year, month, and day values, from a cell reference or formula result and you get the date.
DATE Syntax
DATE(year,month,day)
year, required, 4-digit positive integer numbers.
- If you fill number between 0 until 1899, then Excel added to by 1900.
- If you fill number between 1900 until 9999, then Excel processes it as a year.
- If you fill negative number or more than 9999, then Excel returns a #NUM! error.
month, required, integer numbers, positive or negative. 1 represents January and 12 represent December.
- If you fill a positive number adds a month, a negative number reduces the month.
- If the addition of the month exceeds December (last month), it will add years, and if the deduction exceeds January (first month), it will reduce the year.
day, required, integer number, positive or negative, representing days from numbers 1 to 31.
- If you fill a positive number adds a day, a negative number reduces the day.
- If the addition of the day exceeds the end of the month, add the month. If the deduction exceeds the beginning of the month, Excel reduces the month.
How to Use DATE Function in Excel
For example, there is data like the image below. How do the DATE function results in column D?
DATE Function #1
=DATE(A2,B2,C2)
The result is January 31, 2018. The results correspond to the numbers entered in the year, month and day arguments
DATE Function #2
=DATE(A3,B3,C3)
Why is the result the same as the first DATE function?
Please read the DATE syntax explanation above. If you fill the year argument with numbers from 0 to 1899, then Excel will add it to 1900, 118+1900 = 2018, the result is the same year argument = 2018
Be careful with this behavior. Try using the 4-digit number for the year’s argument. Otherwise, the results will be different from what you want, year argument 18 does not mean 2018 but 1918
DATE Function #3
=DATE(A4,B4,C4)
The result is March 1, 2019. February only has 28 days, if the day argument is more than the number of days in the month then excel will add months.
DATE Function #4
=DATE(A5,B5,C5)
The result is February 29, 2020. Why doesn’t, the result be March 1, 2020? Because 2020 is a leap year, February has 29 days.
DATE Function #5
=DATE(A6,B6,C6)
February 2020 with day argument 0 equal to 1 day before.
DATE Function #6
=DATE(A7,B7,C7)
January 31, 2020 minus 32 days, the result is December 30, 2019.
DATE Function #7
=DATE(A8,B8,C8)
month argument 0 is the same as December 1, the previous year.
DATE Function #8
=DATE(A9,B9,C9)
December 1, 2020 minus 12 months equal to December 1, 2019
DATE Function #9
=DATE(A10,B10,C10)
The result is a #NUM error!
day and month arguments can accept negative numbers, but year argument cannot.
DATE Function #10
=DATE(A10,B11,C11)
The result is the error #NUM!. Excel is only able to handle dates from January 1, 1900, to December 31, 9999. The DATE function that returns the outside of the range date will produce a #NUM! error.
Please see the image below for the results of the 10 DATE functions above.
