WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » DATE Excel Function

DATE Excel Function

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:

DATE Formula

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.

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

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

DATE Function in Excel Example 1

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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.

DATE Function in Excel Example 2

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.

DATE Function in Excel Example 3

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 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 as vba

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 Excel Function Video

Recommended Articles

This has been a guide to the DATE Function in Excel. Here we discuss the DATE Formula for excel and how to use the DATE function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • VBA Excel Date
  • Inserting Date in Excel
  • DAY in Excel
  • WEEKDAY Function
1 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download DATE Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More