Working with dates can get tricky if you are not careful. However, Excel has made working with date and time 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 Excel. 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.
=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.
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.