Difference between dates as xxYears yy Months zz days

  • Thread starter Thread starter Rich 80105
  • Start date Start date
R

Rich 80105

I have a number of pairs of dates where I wish to show the difference
between each pair in the format of e.g. 3 years 7 months 9 days.

I can work out the yearsand months but don't know how to then get the
number of remaining days allowing for differences in number of days in
a month or year.

Does anyone have a set of formulae or a macro that will produce what I
want?

Thanks
Rich 80105
 
subtract one date from the other and format the result cell using
custom format of

yy "years" mm "months" dd "days"

hope this help
 
use datedif function
=datedif(date1,date2,"y")&" Years "&datedif(date1,date2,"ym") & " months " &
datedif(date1,date2, "md") & "days"
 
Thanks, that's exactly what I needed.

Its a useful function - but wasn't in the Excel help under "Calculate
the difference betwenen trwo dates"!

Regards
Rich
 
In answer to the question "why does Datedif not appear in the list of
functions?" I believe the answer is simple. Simply that (at leat upto
and including Office 97) it is almost completely reliable. I am sure
that the "almost" is the cause of the delisting.

In 99 I did a lot of work for an insurance compnay that used Datedif
extensively. They were getting a small number of anomolies which were
causing significant miscalculation of pension entitlements and pension
premiums.

I can't remember off hand where the problems occurred but I think
certain anniversary dates were a significant problem.

Also there was a difference between either the worksheet function and
VBA (or the worksheet function and a prior (maybe Excel 5) version).

Sorry the details are so imprecise - and they may have been corrected
in later versions - but thought you ought to be aware. I do use datedif
but for non-critical calculations.

Cheers
 
YUP...MAYbe I'm using the beta 2 version, still not develop well.
I cannot found datedif in help, but still works.
 
That's the way it's been for lots of versions. It only appears in help in xl2k.

But Chip's page is more instructive anyway.
 
Back
Top