DATE Formula Excel – How to use Excel DATE Function




Syntax of DATE Formula
Example of DATE Formula
Possible Errors returned by the DATE Formula

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)

date-formula

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 a DATE Formula

how-to-enter-the-date-formula-in-excel

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.

date-format-in-excel

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:

download example of DATE function



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. Habil Miah wrote:

    Hi,

    I am trying use exces to calculate the number of months between a period. for example between Dec/2006 and Dec/2009, everytime I use a formula it calculates the number of days in between the period, what I would like to know is how do I get it to calculate the number of months between the period.

    thanks,

    Habil

  2. Ajay wrote:

    Habil,

    Welcome to da TaB.
    If you have the two dates in cells A1 and A2, you could use something like:
    =(YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1) and get the number of months between the two dates.

    Hope that helps.
    rgds,

  3. Amie wrote:

    Hello there,

    I have an excel spreadsheet that is importing data from an access database. The date fields are formatted like “40003”, and I have formatted that date column correctly so that it appears as “7/9/2009”. However, I am using a formula that combines several cells into one so that they can appear like this “VA – Fairfax,VA – Fairfax CF – 7/9” by using “=R2&” – “&Q2&”, “&R2&” – “&H2&” – “& Format((D2), “MM/DD”)”. I am trying to get the date formatting to work within this expression, but haven’t been able to find out how to convert the general format of the date cells into an actual DD/MM format within my expression.

    Any ideas on how to do this?

  4. Ajay wrote:

    Hi Amie,

    I think this should help you get the correct date:
    =R2&” – ” & Q2 & “,” & R2 &” – “&H2&” – “&MONTH(D2)&”/”&DAY(D2)
    Regards,
    Ajay

  5. Amie wrote:

    DUDE. You totally Rock! Thank you sooooo much!!!

    🙂

  6. Ajay wrote:

    That was nothing miss….wait till you get to array formulas 🙂
    Welcome to da TaB

  7. Naqib wrote:

    Hi,

    I have an exel sheet. I would like the gregorian date which I enter to be automaticlly changed to solar date using an Excel formula.

    appreciate it in advance if any one can help me?

    Thanks

  8. Naqib wrote:

    The below formula will help you to find out the days, months, and years:

    =DATEDIF($B8,$D8,”Y”)&” years, “&DATEDIF($B8,$D8,”YM”)&” months & “&DATEDIF($B8,$D8,”MD”)&” days”

    Thanks
    Naqib from Afghanistan

  9. Smita wrote:

    I am trying to use the Date function to calculate no. of days and months between two dates. But every time i use the formula, it gives a #VALUE error. how do i get rid of it?

    HELPPPPPPPPPPPPPP

  10. Ajay wrote:

    Smita,
    Your comment got caught in the spam filter which was a bit surprising !

    You can try using EOMONTH formula to get to the end of the month. EOMONTH(A1,0) will get you to the end of the present month and EOMONTH(A1,-1) will get you to the end of month for the previous month. After that it’s just a matter of using YEAR and MONTH function to get the year and months and a simple subtraction to get the number of day within the two dates.

    Regards and welcome to da TaB !

  11. farhan wrote:

    i want to enter any word in capital in any cell of excel without caps lock on can u help me in this

  12. farhan naqvi wrote:

    Dear Team,
    is there any way to enter capital word in excel cell without caps lock on.

  13. Ajay wrote:

    Farhan – Keep the SHIFT key pressed and then press the alphabets that you want 🙂
    Regards,

  14. Sharlee wrote:

    Hi
    I am trying to work a formula to add 14 days to a date specified in a cell. (ie) D3 is 18/11/2009, i need E3 to calculate 14 days after D3.
    Many Thanks
    Sharlee

  15. Ajay wrote:

    @Sharlee – You can simply add 14 to the cell and that would give you a date 14 days hence. So enter =D3+14 in cell E3 and that should give you the date you want. (Hint – Dates are stored as a serial number in Excel although they may be displayed at dd-mmm-yy or any other format)

  16. mario wrote:

    Mohon bantuan hitung kordinat geografis sbb :
    -62848.8S (Lintang Selatan), 1060106.9E (Bujur Timur)
    Bagaimaan formula untuk menjani -6.4802 106.01858
    dari exel terima kasih bantuannya.

    Terima kasih,
    mario

  17. Ajay wrote:

    @mario – Is that Malay / Indonesian by any chance? I probably would be able to understand it better if you could convert that into English. Thanks,

  18. Daniel Ferry wrote:

    Ajay,

    Just saw this post for the first time. Following up on your response to Habil, dated July 28, 2009, I’d offer this formula to calculate the number of months between two dates (in A1 and A2):

    =(A2-A1)/(365.25/12)

    Also, I often need to calculate the date of the end of a month in a formula. If A1 has the date of the first of the month, the following formula will give you the date of the end of that month:

    =DATE(YEAR(A1),MONTH(A1)+1,0)

    The surprising thing here is the ZERO for the DAY argument. Works a charm!

  19. EMMA wrote:

    hi, i am trying to get a date formula from highest to lowest but i dont know how to apply for it, could you help me?

  20. Hamid wrote:

    I am looking a formula which add certain period to a given date. For example any one born on 04-10-1960 and what will be the date at the age of 54 Years 11 Months and 20 days

  21. LoveRhyme wrote:

    The following formula is (B1):

    =DATEDIF(C11,AN11,”M”)&” Months & “&DATEDIF(C11,AN11,”md”)&” days”

    Previous date (A1) will update (B1); however, how could I get (C1) to stop (B1) from updating each day when entering current date in (D1) ; I want dates to stand still!

    Thanks!

  22. sin wrote:

    hi:
    pls help for the counting eg: if A1=joint date
    A2 =joint day+3month

    Many thx

    sin

  23. Arthur MCray wrote:

    What is wrong with this formula. I get a value error.
    I am trying to ask if a3=1 then give date 12 months later, if a3=2 give date 6 months later.
    =IF(A3=1,DATE(YEAR(D3),MONTH(D3)+12,1)),(IF(A3=2,DATE(YEAR(D3),MONTH(D3)+6,1)))

  24. Amanda summers wrote:

    I am trying to use excel to calculate the years and months between a employees hire date. I have their hire date in column E using this format 2/19/1972. Is there a specific formula I can use to return the months and years since hire? thank you



Translate

Chinese (Simplified)DutchEnglishFilipinoFrenchGermanHebrewHindiItalianJapanesePortugueseRussianSpanish

treeemap software for excel