DATE formula in Excel returns the number representing the date for a given day, month and year.
The DATE formula is typically used to construct a date by providing the constituting parts such as day, month and year. The minimum and maximum values that a date can take is Office 2003 is January 1, 1900 and December 31, 9999 (if that existed).
DATE Formula Syntax
DATE Formula has three parts:
DATE (year, month, day)
year is the year in which the date would be occurring. The maximum value that the year can take in office 2003 is 9999.
month is the month in which the date occurs. Interesting to note is that the month is not limited to numbers lying between 1 and 12 but can be any number (negative or positive) till such time that the minimum and maximum limits for the date, as mentioned above, are not breached. For Example if you were to write a date function as =DATE(2009,13,1) it would give you a date 13 months ahead of 1-Jan-2009. The result in this case would be January 1, 2010.
day is the portion representing the day part of the date that we are trying to construct. Again, just like the month parameter mentioned above, the day is not limited to numbers lying between 1 and 31 but can be any number (negative or positive) till such time that the minimum and maximum limits for the date, as mentioned above, are not breached. For Example if you were to write a date function as =DATE(2009,4,35) it would give you a date 35 days ahead of 1-Apr-2009. The result in this case would be May 5, 2009. Similarly you could use a negative number generate a date in the past. (You can even enter fractions though that would not be much use.)
Example of a DATE Formula
Let’s us take a look at an example of the DATE formula. Suppose we had the year, month and day provided to us as three separate fields. We wanted to combine all three to generate a date. As shown above, we could write something like =DATE(A2,B2,C2). The result in this case is January 15, 2009.
Formatting a DATE in Excel
Anytime you have present information that contains dates, please ensure that you have them formatted so that the user can at the first glance make them out clearly. There are a number of date formats in use and with each department, company and country following their own nomenclature. I personally tend to like the dd-mmm-yyyy or the mmm-dd-yyyy formats so that there is no room left for ambiguity.
To format a date, while the cursor is on the cell you want to format, press Ctrl + 1. That would open up the format cell option box. Choose the “Number” tab and click on “Date”. In the “Type”, select the format you like.
How to enter the DATE formula in an Excel Sheet
1. Select the cell in which you want to place the formula
2. Type the formula as =DATE(
3. Then enter the year in which the date we are trying to create occurs.
4. Press the comma key (,)
5. Then enter the month in which the date we are trying to create occurs. (Remember that you can use any number negative or positive as mentioned in the first section above.)
6. Press the comma key (,)
7. Then enter the month in which the date we are trying to create occurs.
8. Close the formula by entering the closing bracket ).
Check out the clip above for knowing if the values you’ve entered are in the same order. In the end your formula should look something like this =DATE(2009,6,28)
Possible Errors with the DATE Formula
The DATE formula can result in the following error values:
DATE Formula #NUM! Error
If the parameters specified in the year, month or day cause the resulting date to exceed the minimum and maximum limits (January 0, 1900 and December 31, 9999 in Excel 2003), the DATE function would result in the #NUM! error. Ex. If you wanted to enter 2009 but mistakenly entered 20009 in say =DATE(20009,4,7), that would result in this error.
DATE Formula #NAME? Error
If the parameters have been specified incorrectly (entering string instead of a number), the DATE function can result in the #NAME? error. For example, say you entered “jul” as the month instead of 7 (which is the number representing the month July) and entered =DATE(2009, jul, 30), that would result in the #NAME? error.
You can download an example of DATE formula here or click on the button below: