Date Diff Calculation

[CSSMepps]

Need some help to get the results I need. The formula I started with is this:
DateDiff: DateDiff([Interval],[LastDateDue],[CurrentDate])

This what I want, but my formula does not return this:
If interval yyyy, then June 1, 2004 to May 1, 2006 = 1 (this equal to 1 year
or 365 days)
If interval yyyy, then June 1, 2005 to May 1, 2006 (this is less than 1 year
or 365 days) (formula actually returns 1, I want it to be 0)
If interval m, then April 5, 2006 to May 1, 2006(this is less than a month
or 30 days) (formula actually returns 1, I need a 0)
If interval m, then April 1, 2006 to May 1, 2006 = 1 (this is equal to a
month)
If interval m, then March 15, 2006 to May 1, 2006 (this is less than two
months or 60 days) (formula returns 2, I need a 1,)
If interval m, then March 1, 2006 to May 1, 2006= 2( this is equal to two
months or 60 days)

I need a formula that returns a whole number up to but not over the
difference in years or months or quarters. Any suggestions?

 

[Douglas J Steele]

DateDiff determines how many end-points it crosses. For instance,
DateDiff("yyyy", #12/31/2005#, #1/1/2006#) will return 1, since there's 1
change of year between those two dates

To calculate age, the normal approach is to determine whether or not the
birthday has already occurred in the year. You do this by comparing the
month and day of the Date of Birth to the current month and day and
subtracting 1 from what DateDiff calculates if the birthday hasn't yet
occurred:

DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") < Format([DOB],
"mmdd"), 1, 0)

You have to apply logic like this in all your cases.

You might also check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html