Categorized | others

Excel DATE Function – How to use Excel DATE Function




Syntax of Excel DATE Function
Example of Excel DATE Function
Possible Errors returned by the DATE Function

The Excel Date function returns the number representing the date for a given day, month and year.

In Excel DATE function 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 in Office 2003 is January 1, 1900 and December 31, 9999.

Excel DATE Function Syntax

In Excel, DATE Function has three parts:

DATE (year, month, day)

excel-date-function

year

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

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

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 an Excel DATE Function

how-to-enter-the-excel-date-function

Let’s us take a look at an example of the DATE Function. 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 in Excel, 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.

date-format-in-excel

How to enter the DATE Function in an Excel Spreadsheet

1. Select the cell in which you want to place the function

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 Function

The DATE formula can result in the following error values:

DATE Function #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 Function #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.

Quick Tip: What is the shortcut to insert current date in Excel ?
Ans – Press the Ctrl key and then while holding in down, press the ; key :-)

You can download an example of Excel DATE Function here or click on the button below:

excel DATE function


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Comments and Trackbacks

  1. Pivot Table Quick Tip - Group By Year, Quarter, Month and Date Field | Excel & VBA - da Tab Is On wrote:

    […] got a pivot table on your hands – its source data consisting of three columns – product, date and sales volume over a preiod of […]

  2. Custom Format in Excel - How to Format Date and Time | Excel & VBA - da Tab Is On wrote:

    […] the date of the month as a number. If the date is 21-Nov-2009 and the custom format specified is d, then the display […]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES