DATE(year, month, day)

Returns the date as a date serial number given a year, month, day.


yearThe year component, between 1900 to 9999.
monthThe month component, between 1 and 12.
dayThe day component between 1 and 31.

Remarks

* For an illustrated example refer to the page under Advanced Functions.
* Always enter the years using 4 digits (not 2).
* If the cell has the "General" number format, then this cell is formatted automatically to "dd/mm/yyyy".
* If "year" >= 0 and "year" <= 1899, then 1900 + "year" is used.
* If "year" < 0, then #NUM! is returned. Row 16.
* If "year" > 9999, then #NUM! is returned.
* If "month" > 12, then "month" is added to the last month of the previous year.
* If "month" <= 0, then ABS("month") is taken away from the last month of the previous year. Row 6.
* If "month" < -32767, then #NUM! is returned. Rows 7 and 12.
* If "month" > 32766, then #NUM! is returned.
* If "day" <= 0, then ABS("day") is taken away from the last day of the previous month. Row 3.
* If "day" > the number of days in "month", then "day" is added to the last day of the previous month. Rows 4 and 13.
* If "day" > the number of days in "month" and ("day" + last day of the previous month > 31/12/9999), then #NUM! is returned.
* If "day" > 32767, then 32767 is used.
* If "day" < -32768, then 32769 (positive) is used.
* You can use the DATEVALUE function to return the date serial number given a date in text format.
* You can use the EDATE function to move a certain number of months before or after a date.
* You can use the NOW function to return the serial number of the current system date and time.
* You can use the TEXT function to convert a date serial number into a formatted text string.
* You can use the TIME function to return the time as a decimal given an hour, minute, second.
* You can use the TODAY function to return the serial number of the current system date.
* You can use the YEAR, MONTH, DAY functions to return the integer values from a serial number.
* For the Microsoft documentation refer to support.office.com

 A
1=DATE(2019,12,20) = 43819
2=DATE(2019,12,20) = 20/12/2019
3=DATE(2019,12,-20) = 10 November 2019
4=DATE(2019,12,120) = 29 March 2020
5=DATE(2019,12,-120) = 02 August 2019
6=DATE(2019,15,20) = 20 March 2020
7=DATE(2019,-15,20) = 20 September 2017
8=DATE(0,1,1) = 01 January 1900
9=DATE(1900,1,0) = 00 January 1900
10=DATE(1,1,1) = 01 January 1901
11=DATE(100,1,2) = 02 January 2000
12=DATE(9999,-32767,1) = 01 May 7268
13=DATE(2000,1,30000) = 18 February 2082
14=TEXT(DATE(2019,11,9),"ddd-mmm-yy") = Sat-Nov-19
15=DATE(0,0,0) = #NUM!
16=DATE(-2,7,1) = #NUM!
17=DATE(2000,1,"some text") = #VALUE!
18=DATE(2000,1,invalid_namedrange) = #NAME?

1 - What is the date serial number for the year 2019, the month 12 and the day 20.
2 - What is the date for the year 2019, the month 12 and the day 20. This cell has been formatted with the number format "dd/mm/yyyy".
3 - What is the date for the year 2019, the month 12 and the day -20. The 20 days have been taken away from the last day of the previous month.
4 - What is the date for the year 2019, the month 12 and the day 120. The 120 days have been added to the last day of the previous month.
5 - What is the date for the year 2019, the month 12 and the day -120. The 120 days have been taken away from the last day of the previous month.
6 - What is the date for the year 2019, the month 15 and the day 20. The 15 months have been added to the last month of the previous year.
7 - What is the date for the year 2019, the month -15 and the day 20. The 15 months have been taken away from the last month of the previous year.
8 - What is the date for the year 0, the month 1 and the day 1.
9 - What is the date for the year 1900, the month 1 and the day 0.
10 - What is the date for the year 1, the month 1 and the day 1.
11 - What is the date for the year 100, the month 1 and the day 2.
12 - What is the date for the year 999, the month -32767 and the day 1.
13 - What is the date for the year 2000, the month 1 and the day 30000.
14 - What is the date for the year 2019, the month 11 and the day 9. This date serial number has been converted into the text format "ddd-mmm-yy".
15 - What is the date for the year 0, the month 0 and the day 0.
16 - What is the date for the year -2, the month 7 and the day 1.
17 - If any of the arguments are not numeric, then #VALUE! is returned.
18 - If any of the arguments refer to invalid named ranges then #NAME? is returned.

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top