Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
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
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
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
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 –