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


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=""> <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

  25. Ruzhella wrote:

    Hi, I’ve a code like this in vba :
    dim dt, a
    dt=date(2012,03,26)
    a=format(dt,”dddd”)
    msgbox “today is ” & a ‘where the result is Monday

    My question is how to convert “Monday” into other language, such as “Senin” in indonesian/malay or etc

    thanks for help

  26. Ruzhella wrote:

    Hi guys, forget about my question before, because I’ve finished the code and run good.

  27. cocraze wrote:

    How do I format the Date cell so that I get a full date, like “Tuesday, 1 May 2012″?

    Many thanks!

  28. Dan Lucko wrote:

    How do I find out if a year is a leap year or not?

  29. kamal wrote:

    03/01/1981 – 01/06/2012 = what formula create

  30. kamal wrote:

    03/01/1981 – 01/06/2012 = what create formula

  31. Yogesh Agrawal wrote:

    when you write a month name ‘ July’ in one cell, it can be converted in to number of days represented in that month in next cell i.e. ’31′
    likewise we write ‘April’ it will automatically showing in next cell ’30′
    it may be possible in excel.

  32. Yogesh Agrawal wrote:

    when you write a month name ‘ July’ in one cell, it can be converted in to number of days represented in that month in next cell i.e. ’31′ days.
    likewise we write ‘April’ it will automatically showing in next cell ’30′ days.
    it may be possible in excel.

  33. dolly wrote:

    Hi,

    Please help. I want to count the number of days between to Dates. Dates are in dd.mm.yy format. e.g. A1 = 12.06.12 & B1 = 21.06.12

    Please help.

  34. Luke wrote:

    Hi there,

    I maintain a calibration spreadsheet that lists the date that equipment is due for calibration.

    I was wondering if it is possible to produce a summary detailing the number of equipments that are out of date, nearing calibration, in date and Not Applicable.

    I have been playing with the array: =SUM((IF((J4:J20-(J1)<=0),1,0))) (where J4:J20 is the calibration dates and J1 is todays date), however this returns a #VALUE! error whenever NA is included in the range.

    I was wondering if there is a way it can ignore NA as to not return the error.

    I have played around with ISERROR but to no avail.

    Any help greatly appreciated,

    Many thanks

    Luke

  35. Micki wrote:

    Hi

    Im trying to create a date that is 5 days from my date of origin using business days only. For instance I want 5 days from October 5 WITHOU the weekends. Can you help me?

  36. David wrote:

    On my spreadsheet I have let say a1 is date entered. in b1 i want to show a date 180 day in advance. I have that formula and work great. If I dont have a date to enter I want b1 to be blank but I want the formula to stay there. Any suggections?

  37. Santosh wrote:

    hi,
    I have 30 excel sheets for one month and I need to put each sheet by each date in specified cell of that sheet. Its long process to put date in cell of each sheet. Is there any formula for this….???
    For example for the month of April I have 30 excel sheet. So I want to put in each sheet’s cell the date. Starting from 1st April to 30th April. I know the date formula =NOW() but if you use this formula the date will be keep on changing if I re opened it next day. Any body knows any solution?
    Santosh

  38. Pradip wrote:

    Sir,
    I want to display the month given the period by the user,e.g., when I write 01/11/2013 to 30/11/13; the machine will display November,2013. In spite of that when I write 01/11/13 to 31/12/13, then the machine will display the period of Nov.,2013 to Dec.,2013. How can I solve this problem; please helppppp me.

  39. Rajkumar wrote:

    In excel 2010; I enter Sr. No., Goods Name and Purchase Date.
    I think. i enter Sr. No. and Goods Name only Purchase date should show automatic in third column. What could be formula.

  40. Sharon wrote:

    I’m trying to get a date on my spreadsheet to auto enter when data is entered but I don’t want the date to change. Example: today 12/13/12 I enter in A1 1000 I want B1 to = 12/13/12, then on 12/16/13 A2 I enter 2000 I want B2 to = 12/16/13 but still need B1 to read 12/13/12. I need the date to = the date the data was entered, then protect that formula? Can you help?
    Thank you in advance!!!
    Sharon

  41. Asa wrote:

    Hi, Please help I want a formula that will first check if the information in cell A1 is equal to information in cell A2 and the calculate the number of days in different cell for the dates in B1 and B2. If the information is not the same it shouldn’t calculate.

  42. Sourav wrote:

    How do I type 12-1,9-4 in excel, Whenever I type these numbers I get 1-Dec, 4-Sep.

  43. Scott wrote:

    I am trying to take a date in a specific cell, then have two dates calculated in a different cell in combination with text. The specific date is in cell B2 and I want the cell with the formula to read:
    “Week of (formula for date1) – (formula for date2)”

    So that if the date in the specific cell was 1/20/14, then when the formula is inputted it would read, “Week of 1/13/14 – 1/19/14″

    I have tried the following, “=”Week of “&B2-7&” – ” &B2-1″ but the formula comes out with “Week of 41652 – 41658″

  44. Kerrianne wrote:

    I’m making a spreadsheet for the school i work at, what i need is,if C4 is between the dates 01/04/2002 and 31/08/2002 then D4 needs to say ‘Yes’ if not it needs to show ‘No’ this is to show which pupils were born in the summer.
    It’s driving me mad and any help would be amazing!

  45. ghykneephir wrote:

    I am trying to create a workbook/appointment book. I want to be able to enter the first of the month on sheet one and have it fill in the dates through out the rest of the workbook, eliminating weekends. I know there is a formula that can do this, but not sure how to write it. Please Help!

Subscribe

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

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel