DATE in Excel (Formula, Examples) | How to Use DATE Function in Excel?

Excel Functions Tutorials

Excel DATE Function (Table of Contents)

DATE Function in Excel
DATE Formula for Excel
How to Use DATE Function in Excel?
Usage of DATE Function in Excel VBA

DATE Function in Excel

DATE Function Excel is a built-in function in MS Excel. DATE in Excel allows you to create a date with individual year, month, and day components. The Excel DATE function is especially useful when supplying dates as inputs to other functions like SUMIFS or COUNTIFS, as you can easily assemble a date using year, month, and day values that come from a cell reference or formula result.

DATE Formula for Excel

The DATE Formula for Excel is as follows:

The DATE Formula for Excel has three arguments out of which two are optional. Where,

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

How to Use DATE Function in Excel?

The DATE in Excel is a Worksheet (WS) function. As a WS function, it can be entered as a part of the formula in a cell of a worksheet. Refer to the DATE in Excel examples given below to understand better.

DATE in Excel Worksheet

Let’s look at the examples given below. Each example covers a different use case implemented using DATE function in Excel.

DATE in Excel Example #1 – Get month from the date

MONTH(DATE(2018,8,28))

As shown in the above DATE formula for Excel, MONTH function is applied on the date represented using DATE function. The MONTH function will return the index of the month returned by the DATE function. E.g. 8 in the given example. Cell D2 has a DATE formula for Excel applied and hence the result ‘8’.

DATE in Excel Example #2 – Find out a leap year

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

As shown in the above DATE formula for excel, the DATE in excel will automatically adjust to month and year values that are 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. In leap years, February has 29 days, so the outer Excel 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’. If the month is 2, the DATE formula for excel returns TRUE. If not, the DATE formula for excel returns FALSE.

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

DATE in Excel Example #3 – Highlight a set of dates

In this Excel DATE Function example, a conditional formatting rule is applied to column B. The dates greater than 2005/1/1 are to be highlighted using pink color style. So, as shown in the screenshot, three dates which are greater than the specified date are highlighted in the configured format. The rest two dates which do not satisfy the criteria are left unformatted as there is no rule applied to such dates.

Things to Remember About the DATE Function in Excel

The Excel DATE function returns a date serial number. One must format the result as a date to display it 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 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.
The day can be positive or negative. If day is greater than the days in the specified month, Excel will add day to the first day of the specified month. If day is less than or equal to zero, Excel will subtract the absolute value of the day plus 1 (i.e. ABS(day) + 1) from the first day of the specified month.

Usage of DATE Function in Excel VBA

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

DATE function in Excel VBA Example

Date()

Result: 12/08/2018

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

Dim myDate As String

myDate = Date()

So, myDate = 12/08/2018

You can download this DATE Function in Excel template here – DATE Function Excel Template

DATE Excel Function