DATE Function in Excel
Date function in excel is a date and time function which represents the number provided to it as arguments in a date and time code, the arguments it takes are integers for day, month and year separately and gives us result in a simple date, the result displayed is in date format but the arguments are provided as integers, method to use this formula is as follows =Day( Year, Month, Day) in a sequential based.
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? (with Examples)
The DATE 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 function examples given below to understand better.
Example #1 – Get 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 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 applied and 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 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 DATE function will return the output as 2/29/2000.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
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 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 contains a date belonging to a non-leap year.
Example #3 – Highlight a set of dates
In this DATE Function example, a conditional formatting rule is applied to column B. The dates greater than 2005/1/1 are to be highlighted using a pink color style. So, as shown in the screenshot, three dates that are greater than the specified date are highlighted in the configured format. The rest two dates that do not satisfy the criteria are left unformatted as there is no rule applied 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 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 a day is greater than the days in the specified month, Excel will add 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, 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 Excel Function Video
Recommended Articles
This has been a guide to DATE Function in Excel. Here we discuss the DATE Formula for excel and how to use DATE function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
