calculate the difference between two dates

[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

 

[JE McGimpsey]

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.

 

[Ian Roberts]

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.

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

 

[JE McGimpsey]

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.

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.

 

[JE McGimpsey]

One way:

=(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)-TODAY())/7