# ExcelDateDif - why does 2007 seem different?

#### Rich101

Hi. I have searched as much as possible for an answer but to no avail so would appreciate if anyone could help please. I am using Excel 2007.
I am trying to calculate the difference between 2 historic dates, inclusive of both dates and I am using the DateDif formula.
I need to calculate from (example) 1/9/2006 to 15/4/2012, ideally in 2 seperate fields, years and days.
I calculate manually that should be 5 years and 228 days (including the 1/9/06 and 15/4//2012).
DATEDIF(A2,B2,"y") gives 5 years - fine. DATEDIF(A2,B2,"yd") gives 226 days. However, If I changed the year of my first date to 2007 I get 5 years and 227 days!
I appreciate that I need to +1 to include the start and end date. And I understand that 2012 being a leap year is not taken in to account as it is based on the first date - but 2006 nor 2007 were not leap years!
I can't see that I am missing anything obvious or calculating incorrectly so any suggestions appreciated!
Thanks

#### Becky

##### Webmistress
Welcome to the forum @Rich101

It's not you, it's Excel. The DATEDIF function is known for being buggy I'm afraid. What are you looking to do? Might be that there's a different way to get there.

#### Rich101

Welcome to the forum @Rich101

It's not you, it's Excel. The DATEDIF function is known for being buggy I'm afraid. What are you looking to do? Might be that there's a different way to get there.

Ah ok thanks - I thought I may have just been missing something obvious!

I need to calculate the exact number of years and days between 2 dates, so someone can put in any two dates and it will calculate it. It needs to include those days as well ie 23/08/2015 to 25/08/2016 would be 1 year and 3 days. I liked the DATEDIF for simplicity but if its going to give odd results sometimes I think I'll avoid it. I've tried different things with DATE function etc but keep getting stuck when trying to incorporate leap years in particular.

Thanks for any suggestions!

#### Becky

##### Webmistress
Hmmm I'm not sure. YEARFRAC takes into account leap years, but it gives you the result as a decimal not years and days. Would that suit your purpose or do you need to know days?

eg =YEARFRAC(A2,B2+1,1)

where A2 is your start date and B2 is your end date. You must use the basis of 1 as this looks at the actual number of days between those dates.

Any good...?