DateDiff - Calculate Full months

[Guest]

I have a program that I need to calculate full months between 2 dates. One
date = 20060721 compared to another date of 20050729. When calculating the
full months I should come up with 11. Here is the formula that I have tried
and I do not come up with the correct computation:
=datediff("m", [date1], [date2]) + int(format([date1], "YYMMDD") <
format([date2], "YYMMDD")) +1

 

[Jeff Boyce]

What do YOU mean by "full months"?

Are your "date" fields actually Access date/time data types, or text fields
with digits you are interpreting as dates?

If you have actual date/time fields, what about
DateDiff("m",[date1],[date2])
is NOT working?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

Try:

=DateDiff("m", [date1], [date2]) + Int(Format([date2], "mmdd") <
Format([date1], "mmdd"))

DateDiff("m", [date1], [date2]) will tell you how many "month crossings"
there have been: in other words, it'll return 12 in that case, since it'll
have crossed from July 2005 to August 2005, from August 2005 to September
2005 and so on to June 2006 to July 2006

Int(Format([date2], "mmdd") < Format([date1], "mmdd")) will determine
whether or not the date represented in date1 has already occurred yet in the
same year as date2. In this case, Format([date1], "mmdd") will return 0729,
while Format([date2], "mmdd") will return 0721. Since 0721 < 0729, that
means that July 29th hasn't yet occurred, so you subtract 1.