Excel DATE function can be used when you want to get the date value using the year, month and, day values as the input arguments. The input arguments can be entered manually, as cell reference that contains the dates, or as a result some formula.
It returns a serial number that represents a specific date in Excel. For example, if it returns 44197, it represents the date: 01 January 2021.
=DATE(year, month, day)
Additional Notes
The result displayed in the cell would depend on the formatting of that cell. For example:
It is recommended to use the four-digit year to avoid unwanted results. If the Year value used is less than 1900, Excel automatically adds 1900 to it. For example, if you use ten as the Year value, Excel makes it 1910.
The Month value can be less than 0 or greater than 12. If the Month value is greater than 12, then Excel automatically adds that excess month to the next year. Similarly, if the month value is less than 0, the DATE function would automatically go back that many numbers of months.
Day value could be positive or negative. If the day value is negative, Excel automatically deducts that many days from the first day of the specified month. If it is more than the number of days in that month, then those extra days are added to the next month value. (example below).
Here are some example of using the Excel DATE function:
Example: Getting the Date Serial number using Year, Month, and Day values
You can get the serial number of a date when you specify the Year, Month, and Day values.
In the example above, the DATE function takes three arguments, 2021 as the year value, 3 as the month value, and 1 as the date value.
It returns 44256 when the cell is formatted as ‘General’, and returns 01-03-2021 when the cell is formatted as ‘Date’.
Note that Excel stores a date or time as a number.
The date format can vary based on your regional settings. For example, the date format in the US is DD-MM-YYYY, while in the UK it’s MM-DD-YYYY.
If you have the Year, Month, and Day values in cells in a worksheet, you can use the cell references as shown below:
Example: Using Negative Values for Month or Days
You can use negative values for month and days. When you use a negative value for a month, let’s say -1, it goes back by 1 month return its date.
For example, if you use the formula =DATE(2021,-1,1), it’ll return the date November 1, 2020, as goes back by one month. Similarly, you can use any negative number in months.
The same logic is followed when you use a month value that is more than 12. In that case, it goes to the next year and returns as the date after the months in excess of 12.
For example, if you use the formula =DATE(2021,13,1), it’ll return the date January 1, 2022.
Note: The date format shown in the examples above is based on my system’s settings (which is DD-MM-YYYY). It may vary based on your settings.
Similarly, you can also use negative day values or values that are more than 31, and accordingly, Excel DATE function would adjust the date.
Click here to Read the Complete list of Functions related to Date and Time
Read also: Top 20 Artificial Intelligence project ideas for Beginners
In the vast world of Excel, mastering the art of freezing rows is a skill…
When it comes to Excel, mastering essential formulas can be a game-changer for office professionals.…
Data exists in various sources, formats, and types. The primary objective of a business analyst…
Microsoft Excel is a powerful tool for managing and analyzing data, and it includes a…
Excel is a powerful spreadsheet program used by millions of people worldwide for data analysis,…
Keyboard shortcuts for Microsoft Excel: In the fast-paced world of spreadsheets and data analysis, every…