AgeMonths function - difference in months

[csda1]

Below and attached as a zip file is a User Defined Function (UDF) that computes the number of whole months plus the fractional days between any 2 dates. Read the comments below, as the results you may get are not always obvious. This is the non-trivial solution.


The trivial solution (which is not what is normally needed) is;
AgeMonths=(YEAR(Date2)*12)+MONTH(Date2)-(YEAR(date1)*12)-MONTH(date1)


The non-trivial solution is;

[b]

function AgeMonths as N(DateValue as D,CompareDate as D)
' Created by Computer Systems Design & Associates
' Copyright 2002 Computer Systems Design & Associates, All Rights Reserved
' Last Update: April 19, 2002

' Purpose: This function computes difference in months of 
'			  CompareDate-Date1 and returns a fractional month
'			  If CompareDate is blank, it uses current date
'			  Value can return negative ages (which is to the 
'			  future)

' Input: DateValue - Current Date Value
'							If blank, it uses system date
'			CompareDate - Date to compare to
'							If blank, it uses system date

' Output:Difference in Dates in # of months plus decimal value

' Errors: If DateValue is an invalid date, function returns error
			
' Examples:
'			AgeMonths({01/01/1999},{})					' Get Age in months
'			AgeMonths({01/01/1999},{06/10/1999})	' Get difference in Months
'			AgeMonths({01/01/1999},{})/12				' Get age in years
'			INT(AgeMonths({01/01/1999},{}))			' Get Age in full months only.

' Notes: 
'			To compute Age in Years, just divide the result by 12,
'			as in 
'					AgeMonths({01/01/1999},{})/12
'			Returns 0 if both dates are blank
'			Same day of month for dates always returns integer number of months

'			When the days are different, then the number of whole months from the
'			base date value (DateValue parameter) are computed.
'			Then, the additional days left for the fractional part are determined.
'			This gets real tricky, as the difference depends on the number of days
'			in the previous month.  While not obvious, reversing the 2 date values
'			will not simply return the same absolute value, but the remaining days
'			will be different as the reference is always full months to the 
'			DateValue parameter

'			If you take the integer value of the returned value, it will always 
'			return the correct whole  months (even if the program decimals are a
'			bit off, as occasionally seen with results like xx.99999999).  To get
'			the whole months, just use an expression like
'					INT(AgeMonths({01/01/1999},{06/10/1999}))

'			To get a rounded version, use
'					ROUND(AgeMonths({01/01/1999},{06/10/1999}),0)

' Set inital return value		
AgeMonths=0

' Set System Date.  We do this in case we need the system date
' for both dates and this function operated near midnight where
' both uses could actually end up on different dates (although 
' very doubtful)
sysdate=DATE()

' If DateValue is blank, set date1 to system date
IF TRIM(CDATE(DateValue))==""
	date1=sysdate
ELSE
	date1=DateValue
END IF

' If CompareDate is blank, set date2 to system date
IF TRIM(CDATE(CompareDate))==""
	date2=sysdate
ELSE
	date2=CompareDate 
END IF

' Compare the day of the months, and compute as needed 
IF DAY(Date2) > DAY(Date1)
	' Compute month difference when CompareDate day of the month is greater than DateValue day of the month
	AgeMonths=(YEAR(Date2)*12)+MONTH(Date2)-(YEAR(date1)*12)-MONTH(date1)+((DAY(Date2)-DAY(Date1))/31)
'	trace.writeln("Remaining days="+LTRIM(STR(DAY(Date2)-DAY(Date1))))
ELSE IF Day(Date2) = Day(Date1) 
	' Compute month difference when day of the months are identical
	AgeMonths=(YEAR(Date2)*12)+MONTH(Date2)-(YEAR(date1)*12)-MONTH(date1)
ELSE
	' Last month from Date 2
	LstMonth=Addmonths(Date2,-1)

	' First day of CompareDate
	Date2_1st=Date2-DAY(Date2)+1

	' Last day of previous month
	LstMoLst=Date2_1st-1


' The 2nd line of this expression is the real tricky part
' to compute the remaining days
	AgeMonths=(YEAR(Date2)*12)+MONTH(Date2)-(YEAR(date1)*12)-MONTH(date1)-1
+(DAY(Date2)+DAY(LstMoLst)-MIN(DAY(LstMoLst),DAY(Date1)))/31
'	trace.writeln("Remaining days="+LTRIM(STR(DAY(Date2)+DAY(LstMoLst)-MIN(DAY(LstMoLst),DAY(Date1)))))
END IF

end function

[b]

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]