Date Function In Excel Formula Examples How To Use

DATE Function in Excel

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

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. 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

How to Use the DATE Function in Excel? (with Examples)

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, the MONTH function is applied 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 formattingConditional FormattingA complementary good is one whose usage is directly related to the usage of another linked or associated good or a paired good i.e. we can say two goods are complementary to each other. read more 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.

Things to Remember

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.The day can be positive or negative. If a day is greater than the days in the specified month, Excel will add a day to the first day of the specified month. If a 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 VBA returns the current system date. It can be used in Excel VBA as follows:

DATE function in VBA Example

Date()

Result: 12/08/2018

Here, the 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

Date Function In Excel Formula Examples How To Use