calculate the difference between two dates

  • Thread starter Thread starter Ian Roberts
  • Start date Start date
I

Ian Roberts

I've been trying to calculate the difference between two dates with the
result displayed as number of years, months, days.

I have experimented with the DATED IF function but haven't been able to get
it to work. Maybe it only works with specific dates rather than cell refs
and doesn't like the use of TODAY() within it?

I'd really appreciate some help on the correct formula to use. Gosh - for
something I thought was pretty straightforward its proving brain achingly
frustrating!

e.g. in A59 I have 7-Apr-09 (cell format dd-mmm-yy) and I want to know how
many years, days, months between TODAY() and A59.

Thanks for any info

Ian
 
one way:

=DATEDIF(TODAY(),A59,"y") & "_years,_" & DATEDIF(TODAY(),A59,"ym") &
"_months,_" & DATEDIF(TODAY(),A59,"yd") & "_days"


where spaces are substituted by underscores to prevent unfortunate
linewrap.
 
JE McGimpsey said:
one way:

=DATEDIF(TODAY(),A59,"y") & "_years,_" & DATEDIF(TODAY(),A59,"ym") &
"_months,_" & DATEDIF(TODAY(),A59,"yd") & "_days"


where spaces are substituted by underscores to prevent unfortunate
linewrap.
Click to expand...


Hello JE

I'm very grateful to you. Yes it works a treat!

Ive dropped the display of days as I found this buggy - it gave the *total*
number of days rather than the days before the month end. eg I'd see 3
months 95 days when it should be 3 months 3days.

I'm now experimenting to see if I can get it to show the number of weeks
before the month end. Its curious that the DATEDIF doesnt include weeks. If
you have any ideas how to do this...

Thanks again for your time and help.

Regards

Ian
 
Ian Roberts said:
Ive dropped the display of days as I found this buggy - it gave the *total*
number of days rather than the days before the month end. eg I'd see 3
months 95 days when it should be 3 months 3days.
Click to expand...

You don't need to drop days - I screwed up using "yd", "md" will display
the values you want.

However, the fuzzy definition of "month", it can lead to some strange
results.

DATEDIF("31 Jan 2004","1 Mar 2004","md") ==> -1

since DATEDIF bases the length of the month on the first date.
 
Back
Top