difference between dates

[Guest]

Hi,

I am using this formula to calculate the difference (in months) between two
dates;

=DATEDIF(A2,B2,"m")

However there is a problem. When using the above formula the difference
between these two dates-

1/4/2006 - 31/3/2006

The formual returns 11 months. This isn't correct as they are actually 364
days apart, and the difference I need to see for my purposes is 12 months.

Does anybody have a solution?

Many thanks

 

[Special-K]

Erm, in your example the difference between 01/04/2006 1st April 2006
and 31/03/2006 31st march 2006 is actually 1 day!!

 

[Bill Ridgeway]

This is a weird one.

The difference between 01/04/2005 and 31/03/2006 (not as stated) is indeed
364 days which should (rounded up) come to 12 months.
Your formula does return 11.0 months so ROUNDUP wont work.

Then, I thought it's the age-old trap of whether or not you are counting the
first period. I've come across this trap many times. When calculating date
differences you have to be aware whether or not you are counting the first
period and, if necessary, add one.

There are a number of arguments for determining when the clock 'starts'. Do
you remember the arguments around the turn of this century. Was 01/01/2000
or 01/01/2001? Let's not go into that one again!

Regards.

Bill Ridgeway
Computer Solutions