Want to calculate the number of months between two dates? One little known way is to use the secret Excel DATEDIF function.
Try typing =DATEDIF into Excel and it will pretend it doesn’t recognise it – see how it’s not in the list of functions to the left.
But if you know the syntax, which I’ll share with you in a moment, you can use it for a range of applications.
I suspect it's secret because it can be a bit problematic and occasionally returns errors! You should use it with caution. I'll talk more about the known issues with the Excel DATEDIF function in a moment.
Microsoft say they only include DATEDIF in recent versions for backward compatibility, which is lucky for us because it’s a really handy function.
Now, as you’d expect from its name the DATEDIF function calculates the difference between two dates.
Excel DATEDIF Function syntax
=DATEDIF(date1,date2,unit)
Date1 is the start date
Date2 is the end date
Unit is the type of unit you want to calculate e.g. days, months, years.
DATEDIF Units
D = days. Complete days between two dates.
M = months. Complete calendar months between two dates.
Y = years. Complete calendar years between two dates.
YD = Complete calendar days between two dates as though the end date is in the same year as the start date.
YM = Complete calendar months between two dates as though the end date is in the same year as the start date.
MD = complete calendar days between two dates as though the month and year of the end date is the same as the start date.
Caution! The "M" and "MD" arguments have known limitations. I'll show you some examples soon.
Note: DATEDIF calculations do not include the start date in the resultant count, and they only return whole numbers.
Let’s take a look at some DATEDIF examples:
Note: as I'm in Australia all the dates in this example are dd/mm/yyyy. Please modify your dates to match your region settings when practicing with these examples.
BTW: you could achieve the same result for example 1 above with this formula:
=C4-B4
Ok, so the above are some pretty basic calculations.
Let’s have some fun with DATEDIF now, and in keeping with the Queen’s Silver Jubilee we’ll use Her Majesty as an example.
Fun # 1:
Years since Queen Elizabeth II ascended the throne:
=DATEDIF("6/2/1952",TODAY(),"Y")
= 60
Note: in the above formula TODAY() = 6th June 2012. The TODAY() function will return today’s date as per your computer clock. Also handy for report headers and the like.
Fun # 2:
Queen Elizabeth’s age today (6th June 2012) in days, months and years:=DATEDIF("21/04/1926",TODAY(),"y")&" years,"&DATEDIF("21/04/1926",TODAY(),"ym") &" month(s), "&DATEDIF("21/04/1926",TODAY(),"md")&" Days"
= 86 years, 1 month(s), 16 Days
The above formula uses the concatenation technique to join text and numbers together.
Fun # 3:
Calculate how long until my next holiday.
Suppressing 0 values where there are no years or months returned:
="Only "&IF(DATEDIF(TODAY(),"17/03/2013","y")=0,"",DATEDIF(TODAY(),"17/03/2013","y")&" years ")&IF(DATEDIF(TODAY(),"17/03/2013","ym")=0,"",DATEDIF(TODAY(),"17/03/2013","ym")&" months ")&DATEDIF(TODAY(),"17/03/2013","md")&" days"
= Only 9 months 11 days
This technique uses an IF function to first evaluate whether there are any years to count, if not return nothing (as stipulated by the empty double quotes ""), and so on for months and days.
Ideas for Using DATEDIF:
- Your age in days, months and years. When you get older this gets harder to keep track of 🙂
- Length of service of an employee.
- Equipment age.
- Countdown to a date.
Excel DATEDIF Function Errors
- If Date 1 is later than Date 2 Excel will return a #NUM error.
- If Date 1 or Date 2 is not a valid date you will get a #VALUE error.
- If the unit is not one of the above options Excel will return a #NUM error.
The table below illustrates some examples of the DATEDIF function. The problematic units are M and MD. Cell E36 result should be 2! Something is not right with Feb 28, 2017. And cell E43 contains an example of MD returning erroneous results. It should be 1, not -2. Cells E44 & E45 also return inconsistent results.
DATEDIF Alternatives
Now you see how unreliable DATEDIF can be, it's probably better to err on the side of caution and use a different function to achieve the same results. *Note the M substitutes either round the months up or down. There is no direct equivalent to DATEDIF with Unit "M". Likewise, the alternate to MD doesn't replicate perfectly in every scenario.
Free eBook - Working with Date & Time in Excel
Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.
Enter your email address below to download the comprehensive Excel workbook and PDF.
Excel COUNT Coloured Cells
Genevieve Piercey
How can you turn this into months and years instead of Number days. =IF(AND(AB2>0,TODAY()>=AB2),AB2-G2,TODAY()-G2)
Mynda Treacy
Hi Genevieve,
If you look at example “Fun # 2” in the post above, you can use that formula and just leave off the last section for the ‘days’. If you get stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
JB
I’ve used DATEDIF to make a chart that shows all my family members’ birthdays and current ages in the format “years, months, days.” The formula, split below into three parts to make it easier to understand, is:
= IF(DATEDIF(B27,now(),”y”), DATEDIF(B27,now(),”y”)&” years, “)
& IF(DATEDIF(B27,now(),”ym”), DATEDIF(B27,now(),”ym”)&” months, “)
& IF(DATEDIF(B27,now(),”md”), & DATEDIF(B27,now(),”md”) &” days”,””)
The only problem arises when the date is within the first year. Then the argument returns “FALSE” for the whole first IF clause. So the result is, e.g., “FALSE 5 months, 18 days.” (Oddly enough this does not happen when the months are the same or the days are the same. In that case it will return e.g. “31 years, 20 days” or “31 years, 3 months.”)
How to keep FALSE from appearing?
Mynda Treacy
Hi JB, The FALSE is being included because the concatenation converts it to a text string. One option is this:
Mynda
jim
This doesn’t seem to work with shared workbooks in a browser (along with many other things!)
Better to use the longer workarounds where you know how it works
K Naveen Kumar
Need excel formula to find how many days between two dates, i tried doing minus(-) between to dates (A2-B2), but getting 1day less in this way.
So request you please help with the formula
Mynda Treacy
You can just add a day, like so:
=A2-B2+1
Mynda
Bowz
Is there any way that i can have the end date inclusive? my formula is to calculate the term (year,month,days) for contracts. Thanks!
Catalin Bombea
Hi Bowz,
I guess replacing EndDate with EndDate+1 should be all you need.
Jean-Paul Turgeon
I tried to use the Excel DATEDIFF Function for the following dates (Start Date = 1881-05-20 and End Date = 1920-10-23) but Excel returns the following error #VALUE!
Mynda Treacy
Hi Jean-Paul,
Excel can’t work with dates pre 1/1/1901.
Mynda
Kari
I have leave data for which I need to take into account the
1. Leave start and end date
2. Leave extension date (if any)
3. Late from Leave based on Return from Leave
This needs to be filled in the daily dates as per the leave type (from start date to end date or from start date to leave extension date) or Late – only for the dates which the employee is late
My Typical columns are
1. Sr. No
2. Emp ID
3. Start Date
4. End date
5. Leave Type
6. Leave extended till
7. Actual Return date
and then I dates in the year for which I need the leave type or Late filled in
Any help willl be really appreciated
Catalin Bombea
Hi Kari,
Can you prepare a sample file with your data structure and some manual examples of the expected results? Working with dates is tricky, without a sample file it’s hard to provide a usable answer.
You can use our forum to upload. (create a new topic after sign-up)
Catalin
Rob M
Hi,
Was wondering if you could help me out… using this formula and it is working but returning a *num in a cell that I do not have a date it yet. Is there a way to tell it to not compute unless B10 has a date? L3 is a constant date but B10 changes due to row changes for each appointment ie.. B10, B11 and so on.
=DATEDIF(L3,B10,”y”)&”y “&DATEDIF(L3,B10,”ym”)& “m ” & DATEDIF(L3,B10,”md”)&”d”
Rob M
Also with this one…. =EDATE(I31,3) If l31 is blank to not show a date?
Mynda Treacy
=IF(I31=””,””,EDATE(I31,3))
Mynda Treacy
Hi Rob,
You can hide errors with the IFERROR functionhide errors with the IFERROR functionhide errors with the IFERROR function. e.g.:
=IFERROR(DATEDIF(your formula),””)
Mynda
Rob M
This worked great Thank you Rob
Dean High
Hi, I’d like the datedif counter to work out if an employee has worked a full calendar month, regardless if the start date. For example, if the start date is the 15th March and the end date is the 14th May Datedif will return this as 1 Month but this should actually be 2.
Is there a way this can be achieved?
Catalin Bombea
Hi Dean,
Try this alternative formula:
=YEAR(B1)-YEAR(A1)-(TEXT(B1,"mmdd")< TEXT(A1,"mmdd"))&" years "&MOD(MONTH(B1)-MONTH(A1)-(DAY(B1)< DAY(A1)),12)&" months "&B1-DATE(YEAR(B1),MONTH(B1)-(DAY(B1)< DAY(A1)),DAY(A1))&" days"
vinit
i have two date diff. in excel formula
i.e. 31-10-2018 ( any one date )is count of one month and next 17-05-2019 ( anyone date) i count of one month total month count 8 please suggested excel fourmula
Mynda Treacy
Hi Vinit,
I’m not sure what you mean, but I suspect you want it to calculate from the start of one month to the end of the next month and include those months. If so:
Mynda
Amit Panasara
minor correction…. if remainder >20, it count as month
=12*(YEAR(F7)-YEAR(E7))+(MONTH(F7)-MONTH(E7))+((DAY(F7)+(EDATE(E7,0)-E7))>20)
Catalin Bombea
Hi Amit,
Thanks for sharing your version for months calculation.
Can you check again your formula? Seems to be returning different results on these dates: 14/03/2002 – 20/03/2019 (204 months) and 14/03/2002 – 21/03/2019 (205 months). as you can see, it’s just a day more in the second set of dates, but it adds another month in calculation.
Cheers
Catalin
Kiran
Very helpful, Thank you so much
hayat khan
what is wrong in this formula ?
plz correct it
COUNTIFS(DATE(YEAR(Y:Y,$AU$7,F:F,AS8)
while
Y:Y is a range of different dates
$AU$7 is a specific year
F:F is a range of different designations
AS8 is a specific designation
Mynda Treacy
Hi Hayat,
You can’t nest the YEAR function like this. Try this:
Mynda
Gregory E. Dsouza
Sir,
my problem is that I am operating excel 2007 and the dates gets recorded as 2-01-2017 in this format is this reason I cannot use the datedif function or something different can you sugest.
thanks
Gregory- Goa -India.
Mynda Treacy
Hi Gregory,
Yes, possibly. You can try fixing your data formats with Text to Columns as described here: https://www.myonlinetraininghub.com/excel-text-to-columns-to-correct-date-formats
If that doesn’t work, then please post your question and sample file on our Excel Forum.
Mynda
Aaron
Hello Everyone
I’ve been looking everywhere for help to my edate error but cant find anything that works. Hope someone here can help.
I am trying to use edate to subtract 12 months from a given date to use in a look up calculation for Fiscal YTD results. Everything works fine until February 2017 rolled around and the results give me February 28, 2016 not February 29, 2016. Which causes a #N/A error.
=IF(B8=EDATE(Q3,-12),B10,SUM(B10:INDEX(B10:Y10,1,MATCH(EDATE(Q3,-12),B8:Y8,0))))
Is the formula I am using which works fine for every other month.
Q3 refers to the current month end date: in this case: 2/28/2017
Row 8 has dates in subsequent cells as follows:
7/31/15 8/31/15 9/30/15 10/31/15 11/30/15 12/31/15 1/31/16 2/29/16 3/30/16 etc all the way up to my 2/28/17 month.
the formula returns: 2/28/16 not the 2/29/16 I need to get my prior fiscal year to date total.
In other words my formula returns the date as 42428 not 42429. Which the causes the formula to error out since there is no 42428 in the date cells.
Thanks in advance for the help.
Catalin Bombea
Hi Aaron,
Please upload a sample file on our forum (create a new topic), so we can see your data. Only with EOMONTH you will be able to return the last date in month;
=EOMONTH(Q3,-12)
Catalin
Kathleen
There appears to be some sort of Excel glitch with 2/28/17. I’m using the date diff function described above to calculated # of months from a specified start date as part of an amortization calculation. Once I got to 2/28/17, the formula returns the same value as 1/31/17. However, when I type 3/31/17, it resumes calculating correctly. Weird.
Dustin
This post helped me immensely. Thank you!!
dominique Fischer
does not work in Excel 2016 home version… tried and tried again.
How come?
Catalin Bombea
It should work. Make sure that the first argument receives a date smaller than the date from the second argument, like this:
=DATEDIF(“01/01/2014″,”01/07/2016″,”y”)
The third argument is important too.
Also, if you are using semicolon as the default delimiter, replace the comma with semicolon, to separate the arguments.
NeoTheChoiced
Hi there!
I’m trying to do the same as you here:
IF(DATEDIF(TODAY(),”17/03/2013″,”y”)=0,””,DATEDIF(TODAY(),”17/03/2013″,”y”)&” years
but instead of show “years” every time I want it to show “year” instead, if the value is = 1, and “years” if it’s value 2 or more! on the same cell please!
and Thanks for all!
Mynda Treacy
Hi Neo,
You can use this formula:
=IF(DATEDIF("17/03/2013",TODAY(),"y")=0,"",IF(DATEDIF("17/03/2013",TODAY(),"y")=1,DATEDIF("17/03/2013",TODAY(),"y")&" year", DATEDIF("17/03/2013",TODAY(),"y")&" years"))Although, a better solution would be to change ‘years’ to year(s) like so:
=IF(DATEDIF("17/03/2013",TODAY(),"y")=0,"",DATEDIF("17/03/2013",TODAY(),"y")&" year(s)")Mynda
Juzer
Hi Need to calculate the number of days of column (A1) till today for example 01.09.2016 number of days should be 5 days
Catalin Bombea
Hi Juzer,
Have you tried a simple deduction?
=Today()-A1
The cell with this formula should be formatted as number, not date.
rob
Hi have been using this formula for a while .=DATEDIF(A1,B1,”y”)&”y”&DATEDIF(A1,B1,”ym”)&”m”&DATEDIF(A1,B1,”md”)&”d”. Where A1 is a past date and B1 is =TODAY() . Now the day are counting past 31 for some reason.? 0y 3m 180d . Has not done this before any help please .thanks rob
Catalin Bombea
Hi Rob,
You have to upload a sample file, it will help us understand what is wrong there. Are you using excel 2007? I know there is a bug for DATEDIFF in that version?
If this is the case, try this version without DATEDIF:
=YEAR(B1)-YEAR(A1)-(TEXT(B1,”mmdd”)< TEXT(A1,”mmdd”))&” years “&MOD(MONTH(B1)-MONTH(A1)-(DAY(B1)< DAY(A1)),12)&” months “&B1-DATE(YEAR(B1),MONTH(B1)-(DAY(B1)< DAY(A1)),DAY(A1))&” days” The above formula will give the same result as the formula below which is based on DATEDIF: =DATEDIF(A1,B1,”y”)&” year(s), “&DATEDIF(A1,B1,”ym”)&” month(s), “&DATEDIF(A1,B1,”md”)&” day(s)” Cheers, Catalin
Dale Boycott
need to calculate the days between a start date (A1) and today but to stop calculating when an end date is entered in cell (A2) so (A3) will show how many days have passed but when I open the file after the end date I don’t want the calculation to continue going forward.
Thanks
Dale
Catalin Bombea
Hi Dale,
Try this in A3:
=IF(AND(A2>0,TODAY()>=A2),A2-A1,TODAY()-A1)
It will stop counting is A2 is below today’s date.
Cheers,
Catalin
Jamie G
Hi,
I am using the following formula:
=IF(K1>0,DATEDIF(K1, J5, “Y”),””)
To calculate a birthdate up to the end of each year, so one is 12/31/2014, another 12/31/2015, and so on. They all work just fine until I get to 12/31/2016. For some reason the formula then returns the same result as it does for the 2014 date. I have it so the K1 cell is the DOB and the J5 is the 12/31/2016 date.
I can’t for the life of me figure out what I have missed since there are 4 other year ending dates that are calculating perfectly.
Thanks,
Jamie
Catalin Bombea
Hi Jamie,
What version of excel are you using? Excel 2007 has a bug in DATEDIF function.
If this is the case, try this version without DATEDIF:
=YEAR(B1)-YEAR(A1)-(TEXT(B1,”mmdd”)< TEXT(A1,"mmdd"))&" years "&MOD(MONTH(B1)-MONTH(A1)-(DAY(B1)< DAY(A1)),12)&" months "&B1-DATE(YEAR(B1),MONTH(B1)-(DAY(B1)< DAY(A1)),DAY(A1))&" days" The above formula will give the same result as the formula below which is based on DATEDIF: =DATEDIF(A1,B1,"y")&" year(s), "&DATEDIF(A1,B1,"ym")&" month(s), "&DATEDIF(A1,B1,"md")&" day(s)" Cheers, Catalin
Alan
Catalin
Just wanted to say thank you, the response you posted to Jamies query has resolved my problem that I was having (see post below on June 12 2015).
Catalin Bombea
Hi Alan,
That’s very kind of you, glad to hear that the problem is solved 🙂
Cheers,
Catalin
Alan
I hope you can help, I am having a problem with DATEDIF, and 1 particular date. I have a spreadsheet with a date of M25= (30/12/2014) and the other date AI25 = (01/01/16). UsIng the following formula:
DATEDIF(M25,AI25,”y”) & ” year(s), ” & DATEDIF(M25,AI25,”ym”) & ” month(s), ” & DATEDIF(M25,AI25,”md”) & ” day(s)”)
I should get a response of 1 year(s), 0 month(s), 2 day(s). Instead I am getting 1 year(s), 0 month(s), 115 day(s).
I have tried testing it in a different spreadsheet with the same dates and get the same results. I have changed the AI25 value to 01/01/15 and it gives the correct result of 2 days. I have changed the AI25 value to 01/01/17 and it returns 2 years 2 days. I have chagned the M25 value to 30/11/14 and it returns 1 year 1 month 115 days.
For some reason it doesn’t like 01/01/16, any thoughts?
Catalin Bombea
Hi Alan,
I was not able to replicate the situation you described, my result with the 2 dates provided and your formula returns 1 year(s), 0 month(s), 2 day(s)
Can you attach a sample file on our Help Desk? I have to see the file, there may be other problems.
Cheers,
Catalin
Dr. . Polychronopulos
Excellent article well written with good examples.
The ONLY article that worked OK on this obscure function after trying 5 others, following a Google search on DATEDIF.
Well Done!
Mynda Treacy
Thanks! Glad you found it useful.
Clint
I need to calculate a 30 calendar day period which includes any sat/sun or holiday during the 30 days but sat/sun and holidays are not considered for the last day.
Catalin Bombea
Hi Clint,
You can check if the start date + 30 days is saturday, add 2 more days; if the result falls on a sunday, just add 1 more day to result.
Like:
=If(Weekday(A1+30)=6,A1+32,If(Weekday(A1+30)=7,A1+31,A1+30))
Catalin
tina
Hi there,
I am trying to calculate the number of years my employees have been with the company. I had the function good until I got to an open position where there was no start date; now, on that line it wants to calculate the number of days in the year up to todays date. I tried the only if function but it keeps telling me the value is wrong. I am using the “C6” instead of a date. The column C has all my dates…
Catalin Bombea
Hi Tina,
You can change the formula from:
=DATEDIF(C6,NOW(),”d”)
to:
=DATEDIF(IF(LEN(C6)=0,”01/01/2014″,C6),NOW(),”d”) or: =DATEDIF(IF(LEN(C6)=0,”01/01/”&YEAR(NOW()),C6),NOW(),”d”) (almost the same, only the year is dynamic)
This way, if C6 is empty, the function will use “01/01/2014” as the start date.
Catalin
Jawa Herath
This is the first time came across such a hidden but very useful formula. Thanks for the disclosure without hiding.
Mynda Treacy
You’re welcome, Jawa 🙂
Mr. Greg Hughes
Dear Mynda,
On this internet page:
https://www.myonlinetraininghub.com/secret-excel-function-datedif
…under the heading of “Fun #2”, I could not get this formula to work right until I changed the ‘date syntax’ from “day/month/year” to “month/day/year”.
Probably You formatted your cell for the date in your user preferred ‘date syntax’ ?
After correcting the ‘date syntax’ the formula worked good for me.
Using vba code, a variable could hold the value of the Birthday in the preferred ‘date syntax’ , perhaps, of the user.
Also, some of the results you got on this same internet page, did not match my results. The reason being, today is a different date.
Mynda Treacy
Hi Greg,
Sorry for the confusion. Since I’m in Australia our dates are dd/mm/yyyy.
I’ve put a note about this in the post above so others don’t get confused too.
Cheers,
Mynda.
Puneet
Hi,
Actually i was trying to get the data as 1.5 years.
For example: from 1/02/12 till 1/07/13, it should as 1.4 years. how can i do this? where 1/07/13 is the current date.
Thanks a lot.
Mynda Treacy
Hi Puneet,
You can use this formula:
= 1.4136
I hope that helps.
Kind regards,
Mynda.
Wedzmer
Hello!
I’ve been browsing the net because i’m having problems in using the dates and days function in excel.
I’m trying to make an attendance sheet using excel but I can’t make the dates work properly. I have 25 rows, grouped into 5 representing monday-friday. I’ve been able to work out the month of June this year, but having problems when i tested it on june 2014 date.
For example, I encoded June 2, 2014 as the start date for classes which is Monday, there are 21 class sessions for the whole month which also ends on monday (june 30,2013). Now that means July 1 starts on tuesday, so what will I put on the row for Monday on July, it should be empty right but I want it to be a conditional function that if the last working day (class day) of the previous month ends on a weekday, then the proceeding month must follow on the day that it is supposed to on that certain row.
Can you help me please?
Carlo Estopia
Hi Wedzmer,
This would be better if you will send your concern through HELP DESK.
That is, I mean sending a mock file is the best way to go.
Cheers,
Carlo
Lin
Hi Mynda,
I love this site. Thanks you so much. 🙂
Mynda Treacy
Thanks, Lin. Glad we could help 🙂
HEMANTA MAISNAM
Madam,
Today I have found the long awaiting solution/answer to “how to calculate the age of person by simply knowning his date of birth” using the dateif function in excel. Thanks for your kind online tutorial.
Yours sincerely,
Hemanta Maisnam
Mynda Treacy
Thanks, Hemanta. Glad I could help 🙂
Alice
I tried the formula Fun #2 and it didn’t work. Any ideas.
Mynda Treacy
Hi Alice,
If you work with dates in a mm/dd/yyyy format then you need to alter the arrangment of dates in the above example as mine are in dd/mm/yyyy.
It that’s not the problem then I’d need to see your file to troubleshoot further.
Kind regards,
Mynda.
Kam
Hi Mynda, thank you for wonderful excel tips.
I’ve a question, i want to apply certain formulae for every day except for 1st April of every year. Therefore i don’t want to mention year, otherwise i could use date or datevalue. What should i do to use only month and day check. e.g. if A have dates and C and J and data then:
=IF(A11=DATE(2011,4,1),C11,J10+C11) but i don’t want to use 2011 cauz this restrict it for one year only.
Thanx.
Carlo Estopia
HI Kam,
Before we waste time on doing unusual,
Why don’t you send this first to Help Desk.
I am expecting a file and some illustrations as to how this will all go.
Cheers,
CarloE
Duane Duchesne
DAY START TIME HOURS MINUTES FINISH TIME
MONDAY 3:30 AM 30 4:00 AM
MON/TUE 4:00 AM 27 7:00 AM
TUESDAY 7:00 AM 5 12:00 PM
TUESDAY 12:00 PM 3 30 3:30 PM
TUES/WED 3:30 PM 12 30 4:00 AM
WED/SAT 4:00 AM 89 9:00 PM
SATURDAY 9:00 PM 6 3:00 AM
SAT/SUN 3:00 AM 31 10:00 AM
SUNDAY 10:00 AM 10:00 AM
My Question is…. is it possible to have start day then have then add the time to it to equal the day of the finish time? meaning Monday plus 24 hours equals Tuesday. this would make my life easier if the work schedule would auto populate the days for me.
Carlo Estopia
Hi Duane,
Try these formulas:
Formula 1: Finish Time
Formula 2: Return Day
Assume your Data starts at A1(including headers)
Read: NESTED IFs
Cheers,
CarloE
Duane Duchesne
Thank you for your help, but something is not working for me, as i enter each line it will automatically add 24hours without me inputing data, into the hours, so i will put monday on the left no hours and tuesday apears on the right.
Carlo Estopia
Hi Duane,
LOL dude.
Try this…
Cheers,
CarloE
Duane Duchesne
that gives me the same problem, if column a = Monday it will automatically return Tuesday in column d without entering data in b or c.
Carlo Estopia
Hi Duane,
If the formula above doesn’t work then please send the file via help desk
with the formula I gave you so I can see what’s wrong.
Cheers,
Carlo
Chi
how come excel doesn’t recognize dates such as 3/21/1685, I’m trying to figure age of death of musicians who have died. Here are a few from my chart that don’t seem to work.
Bach DOB: 3/21/1685 DOD: 7/28/1750 Age:????
Beethoven DOB: 12/16/1770 DOD: 3/26/1827
Is it possible to use DATEDIF, i think it would be cool to calculate more then just the year, and would like to add the years days and months lived to my results
Carlo Estopia
Hi Chi,
Unfortunately, MS Excel allows only workarounds when it comes to these pre-1900’s dates.
And workarounds are only available through VBA. So not even the 2010 version has
answers for this; therefore, goes also your DATEDIF function.
If you’re new to VBA, you’ll usually get a pop up message which will warn you that it
will affect your security etc. etc.– just click anything to trust it.
Anyway, I have here the AgeFunc. You can use this like any other Excel Built In Functions.
It has only two arguments stdate and endate; hence, =AgeFunc(stdate i.e. A1, endate i.e. A2)
will give you difference as a result.
So here’s the deal:
1) ALT + F11 (this will bring you to the vbe window)
2) In the VBE window, Select INSERT menu, add MODULE (take note: not CLASS MODULE)
3) Copy and paste this in the module:
Public Function AgeFunc(stdate As Variant, endate As Variant) ' Dim our variables. Dim stvar As String Dim stmon As String Dim stday As String Dim styr As String Dim endvar As String Dim endmon As String Dim endday As String Dim endyr As String Dim stmonf As Integer Dim stdayf As Integer Dim styrf As Integer Dim endmonf As Integer Dim enddayf As Integer Dim endyrf As Integer Dim years As Integer ' This variable will be used to modify string length. Dim fx As Integer fx = 0 ' Calls custom function sfunc which runs the Search worksheet function ' and returns the results. ' Searches for the first "/" sign in the start date. stvar = sfunc("/", stdate) ' Parse the month and day from the start date. stmon = Left(stdate, sfunc("/", stdate) - 1) stday = Mid(stdate, stvar + 1, sfunc("/", stdate, sfunc("/", stdate) + 1) - stvar - 1) ' Check the length of the day and month strings and modify the string ' length variable. If Len(stday) = 1 Then fx = fx + 1 If Len(stmon) = 2 Then fx = fx + 1 ' Parse the year, using information from the string length variable. styr = Right(stdate, Len(stdate) - (sfunc("/", stdate) + 1) - stvar + fx) ' Change the text values we obtained to integers for calculation ' purposes. stmonf = CInt(stmon) stdayf = CInt(stday) styrf = CInt(styr) ' Check for valid date entries. If stmonf < 1 Or stmonf > 12 Or stdayf < 1 Or stdayf > 31 Or styrf < 1 Then AgeFunc = "Invalid Date" Exit Function End If ' Reset the string length variable. fx = 0 ' Parse the first "/" sign from the end date. endvar = sfunc("/", endate) ' Parse the month and day from the end date. endmon = Left(endate, sfunc("/", endate) - 1) endday = Mid(endate, endvar + 1, sfunc("/", endate, sfunc("/", endate) + 1) - endvar - 1) ' Check the length of the day and month strings and modify the string ' length variable. If Len(endday) = 1 Then fx = fx + 1 If Len(endmon) = 2 Then fx = fx + 1 ' Parse the year, using information from the string length variable. endyr = Right(endate, Len(endate) - (sfunc("/", endate) + 1) - endvar + fx) ' Change the text values we obtained to integers for calculation ' purposes. endmonf = CInt(endmon) enddayf = CInt(endday) endyrf = CInt(endyr) ' Check for valid date entries. If endmonf < 1 Or endmonf > 12 Or enddayf < 1 Or enddayf > 31 Or endyrf < 1 Then AgeFunc = "Invalid Date" Exit Function End If ' Determine the initial number of years by subtracting the first and ' second year. years = endyrf - styrf ' Look at the month and day values to make sure a full year has passed. If stmonf > endmonf Then years = years - 1 End If If stmonf = endmonf And stdayf > enddayf Then years = years - 1 End If ' Make sure that we are not returning a negative number and, if not, ' return the years. If years < 0 Then AgeFunc = "Invalid Date" Else AgeFunc = years End If End Function ' This is a second function that the first will call. ' It runs the Search worksheet function with arguments passed from AgeFunc. ' It is used so that the code is easier to read. Public Function sfunc(x As Variant, y As Variant, Optional z As Variant) sfunc = Application.WorksheetFunction.Search(x, y, z) End Functionsource: Microsoft Support
Sincerely,
CarloE
Joze
Hello Mynda,
Your internet side is super and your descriptions of formulas are great. Because of that I sent you a question about formula DATEDIF but you probably did not get it.
I have a problem. When I use formula DATEDIF for calculating a person’s age calculation which I get does not match. Apparently formula has difficulty with leap years. Am I right? How to improve it?
I.E. When I calculate age of a person who was born 26.12.1947 and when I want to know his age at 1.1.2012, calculation is wrong. According to this formula, the person’s age is 64 years, 0 months and 119 days.
What is wrong?
I’ll highly appreciate your help.
Kind regards
Joze
Mynda Treacy
Hi Joze,
I’m not sure where you’re going wrong, but if I use this formula:
=DATEDIF("26/12/1947","1/1/2012","y")&" years ,"&DATEDIF("26/12/1947","1/1/2012","ym") &" month(s), "&DATEDIF("26/12/1947","1/1/2012","md")&" Days"I get:
64 years, 0 month(s), 6 days
Kind regards,
Mynda.
Joze
Hello, Mynda,
thank you for your prompt reply. Unfortunately, I have to tell you that your formula on my computer does not give an accurate result. What’s wrong, I do not know. At first I thought that this is due to the record date. In my country the dot delimiter between DDMMYYYY. Your formula uses as delimiter slash and – gives wrong result.
26.12.1947
1.1.2012
64 years; 0 months; 119 days
=DATEDIF(A1;A2;”y”) & ” years; “&DATEDIF(A1;A2;”ym”) & ” months; “&DATEDIF(A1;A2; “md”) & ” days”
Maybe you can help me to solve this problem.
Kind regards
Joze
Joze
Hello, Mynda,
I browsed the internet and found out what might be the reason for the miscalculation. The reason is that DATEDIF is an undocumented EXCEL function and probably unsupported so it appears to be broken in EXCEL 2007 at Service Pack 2.
This seems to be the reason for my problems.
Kind regards
Joze
Dennis
Hi Mynda,
Can you give me a solution on below problem, i got the answer but if i validated it for ex. i change the current date as 24/01/2012 answer will be -113?
Create a solution related to date – when a certain birth date is entered on the declaration cell solution will tell how many days before the birthday celebration base on the current date.
BirthDate CurrentDate Period before celebration
25/12/1982 24/01/2012 -113 day(s)
Answer: =30-DATEDIF(A17,B17,”MD”)&” day(s)”
Please help thanks!
Mynda Treacy
Hi Dennis,
You can use this formula:
Where A2 contains the date of birth.
Note: replacing TODAY() with the date 24/1/12 gives 336 days with the above formula
Kind regards,
Mynda.
Dennis
Dear Mynda,
Thanks for your prompt reply.
I was asking if how many days before my birthday. With the given dates as 25/12/1982(date of birth) & 24/01/2012(current date) day count should be 1. What would be my formula to get 1 day, please also include the month.
Answer should be 11 months and 1 day.
Regards,
Dennis
Mynda Treacy
Hi Dennis,
Sorry, in your original question you just asked for the number of days. If you want days represented as days and months you can use this:
Answer: 11 m 1 d
Your date of birth in cell A1.
Your date 24/01/2012 in cell B1.
Kind regards,
Mynda.
Dennis
Dear Mynda,
Many thanks! It helps me a lot 🙂
Best regards and more power!
Kosal Kong
it hard to to get the soft doc of 100 excel tips
Mynda Treacy
Hi Kosal,
I’m not sure why you’re having difficulty downloading the 100 Excel Tips e-book. You just need to sign up for the Excel Newsletter here and you’ll be taken straight to the page where you can download it.
Kind regards,
Mynda.
Mahesh Singh
Hi, Good Eveningm
I have just started reding your 100 excel tips and geting much and more excited after knowing new things. Let me go through then I will comment further, till now it is marvellous!!!
Mynda Treacy
Hi Mahesh,
Glad you’re liking it.
Kind regards,
Mynda.
Scott Wilson
looking for tips on Excel
Mynda Treacy
Hi Scott,
You can find Excel tips here.
Kind regards,
Mynda.
DC
Just wanted to mention the lack of documentation is due to the origin of this function; Lotus 123. Datedif() and eomonth() were two popular Lotus formulas they had to support.
Mynda Treacy
True. And I’m glad they did support them as they’re both handy. Thanks, DC.
deepa Prakash Shinde
I like your Excel tips. but can you share with me some other exciting excel tips
Mynda Treacy
Hi Deepa,
Here’ll you’ll find a list of tutorials on Excel Formulas and other Excel tools like PivotTables.
I hope that helps.
Kind regards,
Mynda.
Phil
I love your site and learn something new each time.
Mynda Treacy
Cheers, Phil 🙂