Excel Trivia | Everything about Excel Function and Formulas: DATE & TIME

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

MONTH Function is used to know the month of the date given. MONTH Function will return between 1 to 12. Which month is described in the data format.
You can use MONTH Function to feed in other formulas also.

Syntax of MONTH Function

=MONTH(serial_number)

Argument for MONTH Function

serial_number: it is the value from which we get the month number.

Behavior of MONTH Function

Here we have taken four different date format from the format cell option.
First date is
15-09-2018: it will return 9(September)
Second date is
12 January 2018: it will return 1 (January)
Third date is
2018-12-15 : it will return 12 (December)
Fourth date is
08-04-2018: it will return 4 (April)
The MONTH Function will return you an integer between 1 to 12 even if you use any date format which is pre-specified in the Microsoft Excel.

Usage of MONTH Function

To know the month of the given date

Return of MONTH Function

MONTH Function will return an integer value between 1 to 12.

Error Values

Error Commands	Description
#VALUE	You have written a date in text format
1	You have entered nothing or other than date format

More Example

Example 1 To find the date from the same month

Here we are trying to find the date with same month

Here we have taken two columns with dates and we are using the formula to know the month of the two dates are equal or not

If both the dates have the same month then it will return TRUE otherwise it will return FALSE

=MONTH(B4)=MONTH(C4)

Here for the first dates
15-09-2018 and 19-08-2018: Both dates are from the different month so it will return FALSE

Second Dates
12-01-2018 and 31-01-2015: Both dates are from the same month so it will return TRUE

Third Dates
15-12-2018 and 21-08-2018: Both dates are from the different month so it will return FALSE

Fourth Dates
08-04-2018 and 01-04-2018: Both dates are from the same month so it will return TRUE

Example 2 To convert month number to the month name

Here we take the same example to convert month number to the month name

Here we have used TEXT Function to convert month name into text

=TEXT(B4, "mmmm") It will return full name of the month

=TEXT(B4, "mmm") It will return first three letters of the month

First Date
15-09-2018: it will return September and Sep

Second Date
12-01-2018: it will return January and Jan

Third Date
15-12-2018: it will return December and Dec

Fourth Date
08-04-2018: it will return April and Apr

How to use DATE Function | Microsoft Excel