# DATE Formula Excel – How to use Excel DATE Function

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

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 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:  ## 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>

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,

=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

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 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?
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!

46. Revmm wrote: Years ago I had an Excel spreadsheet that would color an entered date in green until it passed 6 months then it would turn red. I don’t remember how I did that.

47. kattie wrote: tying to get the date for Friday for a year?

48. Amol wrote: Hello All, I have a spreadsheet in which I have to copy and paste data from pdf file. When ever I paste date in suppose cell A2 as “4/7/013” it has text property (by default) I need converted date in cell B2 as “04/07/2013” whenever I copy and paste date in A1.
I have tried custom formatting, text function but I am unable to display as “04/07/2013”. Please help me.

49. Josie wrote: I need to separate a date into separate columns. Example: 03/29/1999. One column for the month = 3 and another column for the date = 29.

50. malki wrote: i need to add 57 years to the birthday

51. SONAL wrote: CONVERT 39 MONTH TO YEAR

52. Mali wrote: Hello I need help I have a spread sheets that has dates of payments. And on this spread sheet there maybe two different dates in different cells. I want to have all dates in one cell on another spreadsheet is there a formula for that.

53. Rick wrote: I have a formula that I am using IF(K4=”1-90″,N4*50,If(K4=”1-100″,N4*32,IF(K4=”1-250″,N4*14.57,))) Excel keeps telling me that there is a problem with this formula even though it works and does what I want it wants to format 1-90 as a date can’t figure out how to stop this.

54. Chaithra wrote: Hi,

I am trying to calculate the end date when start date, difference of no of years, month and dates b/w start date and end dates are given

55. Lorraine wrote: Can I enter a formula in cell to add two weeks to previous date cell. (I.e. track a new due date two week from receipt of data)

56. jr wrote: What is the formula I use if I need to have a cell automatically shows a value or text IF another cell which has dates falls off on a certain day of the month? Thanks

57. Gena wrote: I am trying to calculate -birthday+todaydate=age

58. sairam wrote: Hi Guys,
In my excel sheet 1000 of records are there but the dates are not in ascending order. i want the date(day,month,year) are in sorting order. im using excel 2013.
please give the solution for that,

Thanks,
sairam.

59. linda owen wrote: I am making a trial calendar for my law firm. I need to calculate, for example, the date of trial -100 days. They need to be calendar days, not work days, and I have already set up a list of holidays for the next two years. I cannot figure out how to do the formula for the date -100 CALENDAR days, including holidays. Can someone please help me. I have been working on this for days. I have it totally figured out for the dates that need to be WORK DAY, but cannot figure out the ones for calendar days. Any help would be greatly appreciated. NOTE: I am working in Excel 2003

60. hcordes wrote: Can you tell me how to create a field/formula that would allow me to enter todays date and have it tell me several different future dates based off of a number of days to add to it? This is for a quick reference for checking the outdating of products. Some expire in 28 days, 45 days, 56 days, 6 months, 18 months. I’m wondering if I could easily make a chart to show me those expiration dates easily?

61. Abdul wrote: Hi
i am trying use excel formula to calculate example; 9/1/2017 ,
we are using formula =date(year(\$D\$3),month(\$D\$3),1)
after i want to display only date means 1 or day means Friday

thank
Abdul

62. bridgette wrote: i am trying make a formula for track dates so when it’s time for one my staff to had a training my system will inform me

63. Angy wrote: I am trying to extract the year and month on two separate columns from date in Excel 2013.

But I have a condition to fulfill whereby if the date is between 25/12/2018 to 26/01/2019 it should return the month as 01 and year as 2019.

Appreciate if someone could advise the formula.

1. Fort Worth Date To Date Calculator Excel | datingeffect.com wrote:

[…] 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 …… […]

#### Subscribe

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