DATE Function in Excel

The DATE function in Excel is a date and time function representing the number provided to it as arguments in a date and time code. The arguments it takes are integers for a day, month, and year separately and give us the result in a simple date. The result displayed is in date format, but the arguments are provided as integers. Therefore, we can use the formula: =DATE( Year, Month, Day) on a sequential basis.

For example, = DATE(2020,5,1) equals May 1, 2020.

The DATE formula for Excel is as follows:

The DATE formula for Excel has three arguments, out of which two are optional. When,

• year = The year to use while creating the date.
• month = The month to use while creating the date.
• day = The day to use while creating the date

The DATE is a Worksheet (WS) function. It can be entered as a part of the formula in a worksheet cell as a WS function. You may refer to the DATE function examples given below to understand better.

Example #1 – Get A Month From The Date

MONTH(DATE(2018,8,28))

As shown in the above DATE formula, we can apply the MONTH function on the date represented using the DATE function. The MONTH function will return the month index produced by the DATE function. E.g., 8 in the given example. Cell D2 has a DATE formula, hence the result ‘8’.

Example #2 – Find Out A Leap Year

MONTH(DATE(YEAR(B3),2,29)) = 2

As shown in the above DATE formula, the DATE will automatically adjust to month and year values out of range. Here, the innermost formula is YEAR with parameters as cell B3 indicating the input data, 2 is the index of February month, and 29 for the day. For example, February has 29 days in leap years, so the outer DATE function will return the output as 2/29/2000.

In case of a non-leap year, DATE will return the date March 1 of the year because there is no 29th day, and DATE would roll the date forward into the next month.

The outermost function, MONTH, would extract the month from the result. E.g., 2 or in case of a leap year and 3 in case of a non-leap year.

Further, the result is compared with a constant ‘2’. For example, if the month is 2, the DATE formula in excel returns “TRUE.” If not, the DATE formula returns “FALSE.”

In the following screenshot, cell B2 contains a date belonging to a leap year, and B3 has a date belonging to a non-leap year.

Example #3 – Highlight A Set Of Dates

A conditional formatting rule is applied to column B in this DATE function example. The dates greater than 2005/1/1 are highlighted using a pink color style. So, as shown in the screenshot, three dates greater than the specified date are highlighted in the configured format. The other two dates that do not satisfy the criteria are left unformatted as no rule applies to such dates.

The DATE function in VBA returns the current system date. It can be used in Excel VBA as follows:

Date()

Result: 12/08/2018

Here, the Date() function returns the current system date. We can assign the same to a variable as follows:

Dim myDate As String

myDate = Date()

So, myDate = 12/08/2018 

• The Excel DATE function returns a date serial number. One must format the result as a date to display the date format.
• If the year is between 0 and 1900, Excel will add 1900 to the year.
• A month can be greater than 12 and less than zero. If the month is greater than 12, Excel will add a month to the first month in the specified year. If the month is less than or equal to zero, Excel will subtract the absolute value of the month plus 1 (i.e., ABS(month) + 1) from the first month of the specified year.