[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More
Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
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
The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
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
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.