Go Back

How to Use the Excel DATE Function

Working with dates can get tricky if you are not careful. However, Excel has made working with date and time data a lot easier in the recent versions. It has some wonderful built-in functions to work with dates. One of these functions is the DATE function. In this tutorial, you will learn how to use the Date function in Excel.

How to use the DATE function in Excel

The DATE function helps you create a date from the date, month and year values in Excel. It returns the sequential serial number representing the date from the provided arguments.

Syntax using DATE

=DATE (year, month, day)


This value is required. The year argument can take a value from one to four digits. The year is interpreted by Excel according to your computer’s date system.


This value is also required. The month argument is an integer that can be either positive or negative. It represents 1 to 12 which is the month of the year from January to December.


It is also a required value. The day value is a whole number that represents the day of the month from 1 to 31.


The following examples contain some uses of the DATE function in Excel. The data contains the columns year, month, date, formula used and the formatted dates.

    Figure 1. DATE Function Examples



This example shows the use of a regular DATE function. The arguments year, month and date, are provided inside the DATE function, and Excel returns the serial number and represents the date 9/13/2018. Keep in mind that the year value remains the same if the year argument has the value between 1900 and 9999.


In this example, the value provided for the year argument is 17. When the year argument is between 0 to 1899, the DATE function adds it to 1900 to find out the year. Here, 17 is added to 1900, and you get the date 7/21/1917.


The formula in this example has a month value that is greater than 12. In such cases, the year value will increase by one for every 12 months. This formula has 45 as the month value, for each 12 months Excel adds a year with 2015, and the resulting date is 6/9/2018.


The month argument value for this example is -45. If the month value is negative, Excel subtracts the value from the number of months, added by 1, from the first month of the given year. Here, Excel subtracts 45 from the first month of 2015 which gives the result 3/6/2011.


If the day argument is greater than the days in the month of the month argument, the day value will be the first day of the month plus the argument value of the day. The day argument 53 adds 53 to the month 6 and returns the serial number showing the date 7/23/2018.


The date argument of the DATE function is negative here. Hence, day returns the number by subtracting the value from the first day of the provided month value. Here, Excel subtracts the value 53 from the first day of month 6. As a result, you get the date 4/8/2018.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hello, I need help with the following logic: Logic for GTIN Logic = In Market Active = Yes Approval Status = Approved Start of Ship Date = Start of ship date equal to or Prior to todays date Latest approval date = if the Start of ship date is same then the product with latest approval date. Market Distribution= contain United States Logic for GTIN Logic = Future Active = Yes Approval Status = Approved Start of Ship Date = Closest Start of ship date post todays date Latest approval date = if the Start of ship date is same then the product with latest approval date. Market Distribution= contain United States
Solved by K. U. in 13 mins
Need to know the schedule according to sale date/ last visit date / current date
Solved by G. S. in 16 mins
I need to have cells note red if the date is past the time period based on start date Opening Date 8/28/18 Today's date 6/1/18 Task Due 3 months prior to opening date Date due 5/28/18 - That cell should be red if date is past due
Solved by S. U. in 11 mins

Leave a Comment