Excel DateDif - why does 2007 seem different?

R

Rich101

Joined
Aug 24, 2016
Messages
2
Reaction score
0
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
 
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.
 
Becky said:
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.
Click to expand...

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!
 
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...?
 
Back
Top