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() = 6^{th} 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 (6^{th}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.

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:

Although, a better solution would be to change ‘years’ to year(s) like so:

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:

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

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 🙂