Need to edge out the competition for your dream job? Train for certifications today.
Public Function fnYrsAndMonths(DOB as Variant, Optional TestDate as Variant = NULL) as string
dim intYears as integer
dim intMonths as integer
If IsNull(DOB) then
fnYrsAndMonths = "No DOB provided"
TestDate = NZ(TestDate, Date())
intMonths = DateDiff("m", DOB, TestDate)
'Check for day
if DateSerial(Year(DOB), Month(TestDate), Day(TestDate)) < DateAdd("m", 1, DOB) Then
if intMonths > 0 then intMonths = intMonths - 1
intYears = intMonths\12
intMonths = intMonths - intYears*12
fnYrsAndMonths = intYears & " year" & iif(intYears>1, "s ", " ")
if intMonths > 0 then
fnYrsAndMonths = fnYrsAndMonths & intMonths & iif(intMonths>1, "s", "")
Open in new window
Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Public Function Months( _
ByVal datDate1 As Date, _
ByVal datDate2 As Date, _
Optional ByVal booLinear As Boolean) _
' Returns the difference in full months between datDate1 and datDate2.
' Calculates correctly for:
' negative differences
' leap years
' dates of 29. February
' date/time values with embedded time values
' negative date/time values (prior to 1899-12-29)
' Optionally returns negative counts rounded down to provide a
' linear sequence of month counts.
' For a given datDate1, if datDate2 is decreased stepwise one month from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
' 3, 2, 1, 0, 0, -1, -2
' If booLinear is True, the sequence will be:
' 3, 2, 1, 0, -1, -2, -3
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of months to dates of Feb. 29.
' when the resulting year is a common year.
' 2010-03-30. Cactus Data ApS, CPH.
Dim intDiff As Integer
Dim intSign As Integer
Dim intMonths As Integer
' Find difference in calendar months.
intMonths = DateDiff("m", datDate1, datDate2)
' For positive resp. negative intervals, check if the second date
' falls before, on, or after the crossing date for a 1 month period
' while at the same time correcting for February 29. of leap years.
If DateDiff("d", datDate1, datDate2) > 0 Then
intSign = Sgn(DateDiff("d", DateAdd("m", intMonths, datDate1), datDate2))
intDiff = Abs(intSign < 0)
intSign = Sgn(DateDiff("d", DateAdd("m", -intMonths, datDate2), datDate1))
If intSign <> 0 Then
' Offset negative count of months to continuous sequence if requested.
intDiff = Abs(booLinear)
intDiff = intDiff - Abs(intSign < 0)
' Return count of months as count of full 1 month periods.
Months = intMonths - intDiff
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.