In this tutorial, I am going to explain the syntax and usage of Excel DATE function in Microsoft Excel.
The Excel DATE function returns the date in the Excel recognized serial number format that represents a date. It returns the date based on individual year, month and day components you specify. It is one of the main functions to calculate the dates in Microsoft Excel.
The DATE function is very useful when you need to supply dates as inputs to other Excel functions. There are chances that the supplied date in text format might not recognize by some of the Excel functions. Since DATE allows you to supply all three components (can be a cell reference or result of formulas) separately, assembling them together returns the valid Excel date that guarantees the correct outcome.
For example; =DATE (2018, 4, 30) returns a serial number that represents April 30th, 2018.
DATE (year, month, day)
- year: A number up to 4 digits that represent the year of a date.
- month: An integer value that represents a month of the year, from 1 (January) to 12 (December).
- day: A number representing the day of the month, from 1 to 31.
Examples: Excel DATE Function
Let’s take a few examples to understand the use of DATE function in different situations. `
Return a serial number of date
One of the most obvious uses where you want to simply return a date, but you don’t know the valid system date format or you enter the date in a format which is not considered a valid date by the system that may result in returning an unexpected result.
In the above-given example, the system accepts the valid date in ‘M/D/YYYY’ format whereas initially the date was supplied in ‘D/M/YYYY’ format (left image) that resulted in swapping the day value with the month.
So, it is always better to supply the date using the DATE function that returns the serial number of the valid excel date.
=DATE (2018, 5, 9) returns the serial number 43229 corresponding to May 9, 2018.
Return a serial number of the 1st day of the current year
In the previous example, all three arguments provided directly into DATE function, however, you can avoid supplying date components directly to a DATE formula as the all or any of three components can be retrieved using other Excel date functions.
Let’s say you want a function to always return the serial number for the 1st day of the present year. So, if the excel file will be opened after two years in 2020, it should display the date of the 1st day of 2020, i.e. January 1, 2020.
The formula, =DATE (YEAR (TODAY() ), 1, 1) will always return the serial number representing the 1st day of the current year.
Get the date after 2 years and 5 months from given date
Suppose, you want to add the certain number of years, months and/or days to a particular date given in excel. As the same can be done using other excel methods. Also, I am going to show you how can you use DATE function to get a date after adding years, months, and/or days value to any date.
In the example above, after extracting all three date values using YEAR, MONTH and DAY functions, I have simply added a number 2 and 5 to year and month values respectively and upon execution, DATE returned the serial number ‘43983’ representing 1-Jun-2018.
Additional Notes: –
- If year argument is:
- from 1900 to 9999 inclusive, Excel uses the exact value for the year. For example, =DATE (2018, 4, 1) returns April 1, 2018.
- from 0 to 1899, Excel adds that specified value to 1900 to calculate the year. For example, DATE (118, 1, 1) returns January 1, 2018.
- is less than 0 or greater than 9999, DATE returns the #NUM! error.
- If month argument is:
- greater than 12, Excel adds month to the first month in the specified year.
- less than zero, Excel subtracts the magnitude of that number of months, plus 1, from the first month in the specified year. For example, DATE (2018, -3, 1) returns the serial number representing September 1, 2017 (3 months subtracted from January 1, 2018)
- Same as like the month argument, day argument can be positive or negative. If day argument is:
- greater than the number of days in the specified month, Excel adds that number of days to the first day in the specified month.
- less than or equal to zero, Excel subtracts the absolute value of the day, plus 1, from the first day of the specified month.
I hope the examples and other additional information taken in this tutorial, helped you to have the understanding of DATE function. I will bring more examples of DATE function with other Excel features.