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)
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:
Table Formula in Excel (Something I didn’t Know Till Yesterday) 
CELL Formula in Excel – How to Use CELL Formula and Examples 
Drop Down List in Excel Made Easy 
INDEX Formula Excel – How to use Excel INDEX Function 
MATCH Formula Excel – How to use Excel MATCH Function 
ADDRESS Formula Excel – How to use Excel ADDRESS Function 
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
July 28th, 2009 at 2:37 amHabil,
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.
July 28th, 2009 at 4:16 amrgds,
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?
July 31st, 2009 at 11:16 amHi Amie,
I think this should help you get the correct date:
July 31st, 2009 at 11:29 am=R2&” – ” & Q2 & “,” & R2 &” – “&H2&” – “&MONTH(D2)&”/”&DAY(D2)
Regards,
Ajay
DUDE. You totally Rock! Thank you sooooo much!!!
July 31st, 2009 at 11:33 amThat was nothing miss….wait till you get to array formulas
July 31st, 2009 at 1:30 pmWelcome to da TaB
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
October 19th, 2009 at 2:33 amThe 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
October 19th, 2009 at 2:42 amNaqib from Afghanistan
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
October 22nd, 2009 at 2:53 amSmita,
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 !
October 24th, 2009 at 9:50 ami want to enter any word in capital in any cell of excel without caps lock on can u help me in this
November 7th, 2009 at 2:15 pmDear Team,
November 7th, 2009 at 2:19 pmis there any way to enter capital word in excel cell without caps lock on.
Farhan – Keep the SHIFT key pressed and then press the alphabets that you want
November 8th, 2009 at 1:24 amRegards,
Hi
November 25th, 2009 at 3:20 amI 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
@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)
November 25th, 2009 at 12:59 pmMohon 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,
January 14th, 2010 at 2:57 ammario
@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,
January 14th, 2010 at 12:50 pmAjay,
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!
January 23rd, 2010 at 10:45 pmhi, 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?
September 17th, 2010 at 10:33 pmI 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
November 28th, 2010 at 11:09 amThe 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!
March 7th, 2011 at 11:52 pmhi:
pls help for the counting eg: if A1=joint date
A2 =joint day+3month
Many thx
sin
June 24th, 2011 at 10:32 amWhat is wrong with this formula. I get a value error.
August 26th, 2011 at 10:37 amI 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)))
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
October 12th, 2011 at 9:17 amHi, 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
March 26th, 2012 at 2:34 amHi guys, forget about my question before, because I’ve finished the code and run good.
March 28th, 2012 at 12:02 amHow do I format the Date cell so that I get a full date, like “Tuesday, 1 May 2012”?
Many thanks!
April 27th, 2012 at 7:27 pmHow do I find out if a year is a leap year or not?
May 1st, 2012 at 10:44 am03/01/1981 – 01/06/2012 = what formula create
June 1st, 2012 at 4:23 am03/01/1981 – 01/06/2012 = what create formula
June 1st, 2012 at 4:29 amwhen 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’
June 4th, 2012 at 4:02 amlikewise we write ‘April’ it will automatically showing in next cell ’30’
it may be possible in excel.
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.
June 4th, 2012 at 4:03 amlikewise we write ‘April’ it will automatically showing in next cell ’30’ days.
it may be possible in excel.
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.
June 20th, 2012 at 11:20 pmHi 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
June 21st, 2012 at 7:39 amHi
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?
October 4th, 2012 at 4:58 pmOn 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?
November 20th, 2012 at 9:58 amhi,
August 1st, 2013 at 3:49 amI 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,
November 20th, 2013 at 3:49 amI 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.
In excel 2010; I enter Sr. No., Goods Name and Purchase Date.
December 2nd, 2013 at 3:37 amI think. i enter Sr. No. and Goods Name only Purchase date should show automatic in third column. What could be formula.
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?
December 13th, 2013 at 11:31 amThank you in advance!!!
Sharon
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.
January 2nd, 2014 at 4:17 amHow do I type 12-1,9-4 in excel, Whenever I type these numbers I get 1-Dec, 4-Sep.
January 10th, 2014 at 10:27 amI 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”
January 30th, 2014 at 11:37 amI’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.
February 9th, 2014 at 5:37 pmIt’s driving me mad and any help would be amazing!
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!
July 24th, 2014 at 10:50 amYears 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.
August 7th, 2014 at 10:44 amtying to get the date for Friday for a year?
December 26th, 2014 at 8:50 amHello 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.
February 19th, 2015 at 7:23 amI have tried custom formatting, text function but I am unable to display as “04/07/2013”. Please help me.
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.
June 11th, 2015 at 2:50 pmi need to add 57 years to the birthday
June 30th, 2015 at 5:26 amCONVERT 39 MONTH TO YEAR
August 25th, 2015 at 6:08 amHello 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.
October 29th, 2015 at 10:16 amI 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.
January 8th, 2016 at 1:35 pmHi,
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
January 19th, 2016 at 7:59 amCan 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)
February 1st, 2016 at 10:49 amWhat 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
February 7th, 2016 at 1:44 amI am trying to calculate -birthday+todaydate=age
February 18th, 2016 at 12:09 pmcan you help me please
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,
July 22nd, 2016 at 4:50 amsairam.
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
August 2nd, 2016 at 12:54 pmCan 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?
March 31st, 2017 at 2:53 pmHi
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
September 14th, 2017 at 1:44 amAbdul