Want to calculate the number of months between two dates? You’ll be needing the **secret** 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.

Who knows why it’s a secret.

For some reason Microsoft don’t think the DATEDIF function is worthy of any documentation since Excel 2000. In fact they say they only include it in recent versions for backward compatibility.

Which is lucky for us cause it’s a really handy function.

Now, as you’d expect from its name the DATEDIF function calculates the difference between two dates.

## DATEDIF syntax

=DATEDIF(date1,date2,interval)

Date1 is the start date

Date2 is the end date

Interval is the type of interval you want to calculate e.g. days, months, years.

## DATEDIF Intervals

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.

## Let’s take a look at some DATEDIF examples:

*Since 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 untill my next holiday.

**Supressing 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.

## DATEDIF 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 interval is not one of the above options Excel will return a #NUM error.

Thanks to one of our members, Greg Rosenberger, for reminding me to write about this function. I also had the need to use it again the other day myself.

Gregory E. Dsouza says

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 says

Hi Gregory,

Yes, possibly. You can try fixing your data formats with Text to Columns as described here: http://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 says

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 says

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 says

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 says

This post helped me immensely. Thank you!!

dominique Fischer says

does not work in Excel 2016 home version… tried and tried again.

How come?

Catalin Bombea says

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 says

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 says

Hi Neo,

You can use this formula:

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

Mynda

Juzer says

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 says

Hi Juzer,

Have you tried a simple deduction?

=Today()-A1

The cell with this formula should be formatted as number, not date.

rob says

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 says

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 says

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 says

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 says

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 says

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 says

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 says

Hi Alan,

That’s very kind of you, glad to hear that the problem is solved 🙂

Cheers,

Catalin

Alan says

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 says

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 says

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 says

Thanks! Glad you found it useful.

Clint says

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 says

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 says

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 says

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 says

This is the first time came across such a hidden but very useful formula. Thanks for the disclosure without hiding.

Mynda Treacy says

You’re welcome, Jawa 🙂

Mr. Greg Hughes says

Dear Mynda,

On this internet page:

http://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 says

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 says

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 says

Hi Puneet,

You can use this formula:

= 1.4136

I hope that helps.

Kind regards,

Mynda.

Wedzmer says

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 says

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 says

Hi Mynda,

I love this site. Thanks you so much. 🙂

Mynda Treacy says

Thanks, Lin. Glad we could help 🙂

HEMANTA MAISNAM says

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 says

Thanks, Hemanta. Glad I could help 🙂

Alice says

I tried the formula Fun #2 and it didn’t work. Any ideas.

Mynda Treacy says

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 says

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 says

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 says

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 says

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 says

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 says

Hi Duane,

LOL dude.

Try this…

Cheers,

CarloE

Duane Duchesne says

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 says

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 says

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 says

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 says

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 says

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 says

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 says

Hello, Mynda,

I browsed the internet and found out what might be the reason for the miscalculation. Mr. Rick Rothstein wrote (see: http://www.excelfox.com/forum/f22/recommendation-do-not-use-undocumented-datedif-function-321/) that there is a need for a reconsideration at using the DATEDIF function. 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 says

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 says

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 says

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 says

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 says

Dear Mynda,

Many thanks! It helps me a lot 🙂

Best regards and more power!

Kosal Kong says

it hard to to get the soft doc of 100 excel tips

Mynda Treacy says

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 says

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 says

Hi Mahesh,

Glad you’re liking it.

Kind regards,

Mynda.

Scott Wilson says

looking for tips on Excel

Mynda Treacy says

Hi Scott,

You can find Excel tips here.

Kind regards,

Mynda.

DC says

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 says

True. And I’m glad they did support them as they’re both handy. Thanks, DC.

deepa Prakash Shinde says

I like your Excel tips. but can you share with me some other exciting excel tips

Mynda Treacy says

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 says

I love your site and learn something new each time.

Mynda Treacy says

Cheers, Phil 🙂