Excel DateDif - why does 2007 seem different?

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
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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.
 
Joined
Aug 24, 2016
Messages
2
Reaction score
0
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
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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...?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top