On Tue, 20 Sep 2005 08:07:06 -0700, "Jessica"
wrote:

I am trying to find a function that will allow me to calculate the difference
between two dates. I want the answer to be shown in months only, therefore I
would need any additional days to be converted to a decimal. I tried to the
datedif function, but it does not recognize that the time span between months
may cross over multiple years.

For example I have the following:
Start Date: 04/15/02
End Date: 06/28/03

The number of whole months in between is 14, however their are also
additional days in between and I am looking to convert those to a decimal.
I know the difference in months

What do you mean by a "whole month".

If you mean Calendar month, then there are only 13 "whole months" + 2
fractional months -- in this case 15/30 + 28/30. (I think this is the least
ambiguous method).

If you mean the corresponding day in the End Date month, then you have to
decide how to deal with the issue of what happens if there is no corresponding
day in the End Date month, and also what your denominator will be for the
fractional month (the start month, the end month, 30, 30.416667, etc)

If you use the DATEDIF function, as suggested by another poster, you wind up
with the following:

StartDate EndDate DateDif CalendarMonths
28-Jan-2005 28-Feb-2005 1.00 1.10
29-Jan-2005 28-Feb-2005 1.00 1.06
30-Jan-2005 28-Feb-2005 0.97 1.03
31-Jan-2005 28-Feb-2005 0.93 1.00

There is no "right or wrong"; it's a matter of understanding the results you
obtain. But note that with DateDif, it is possible to obtain the same result
with two different StartDate's.

You also need to decide whether or not you wish to count both the StartDate and
EndDate, or merely subtract the one from the other.


--ron