How to use DATE Function | Microsoft Excel

DATE Function will return you a sequential number which comprises Year, Month and Day and defines a specific date.

Syntax of DATE Function

=DATE(year,month,day)

Arguments of DATE Function

year: it is a year of the date you want. As per the system dates, it counts the year from 1900 to 9999. if you give a year before 1900 the date function will return a random number.

month: it is a month of the date you want. As per the system, the DATE Function counts month from 1 to 12(January to December). If you write more than 12 months the year will be the next year of the sequentially counted month.
For Example: =DATE(2008,15,15) will return date 15-03-2009. 
And for the Same, if you write a negative date then the year will be the previous year of the sequentially counted month.

day: it is a day of the date you want. As per the system, the day should be between 1 to 31. If you write a day more than 31 the DATE Function will count next month of the date sequentially. 
For Example: =DATE(2005,12,32) then the DATE Function will return you a 01-01-2006. And for the same, if you write negative day then date function will return a previous day sequentially

Behaviour of DATE Function

Here we have taken some examples for DATE Function
1 =DATE(2001,5,15) PERFECT
It will give a perfect date in return

2 =DATE(2005,12,32) DAY EXCEED
Here we can see that in place of the day we write 32 which is out of the range because there is no 32nd day in the calendar
So here system will add 1 more day in the date sequence
Here DATE Function will return 1/1/2006 which is the next day of the date we have apply.

3 =DATE(5719,15,13) MONTH EXCEED
Here we can see that in place of the month  we have write 15 which is out of the range because there are only 12 months in the calender
So here system will add three months more and adds one year in the date sequence
Here DATE Function will return 3-13-5720 which is the third month of the next year

4 =DATE(9998,-2,16) MONTH DEXCEED
Here we can see the there is a negative value in the place of the month so systyem will decrease the year by two month and returns the value
Here DATE Function will return 10/16/9997

5 =DATE(10000,3,22) YEAR RANGE EXCEED
Here we can see that there is a error message showing in the result cell
As per the limitaion of the system is 1900 to 9999 so if we write more than 999 the system will retunr an error

6 =DATE(1899,6,25) YEAR RANGE DEXCEED
Here the range of the year is 1900 to 9999 and we have use 1899 so the DATE FUnction will return  a random value.

IMPORTANT NOTES

DATE Function has some limitations that we have learned above.

• Day limitation is 1 to 31 if there is less than 1 and more than 31 value is applied than DATE Function will count previous month or next month respectively
• Month limitation is 1 to 12 if there is less than 1 and more than 12 value is applied than DATE FUnction will count the previous month of the previous year or next month of the next year respectively
• Year limitation is 1900 to 9999 if there is less than 1900 than DATE Function will return random year and if there is more than 9999 than DATE Function will return #NUM Error

Use of the DATE Function

To get a specific excel date

Return of DATE Function

DATE Function will return DATE as per argument arrangement  and system limitation