Option Explicit
Public Function Lifetime(SerialNr As Variant, DateRef As Variant) As Variant
On Error GoTo ErrHandler
Dim strDate As String
Dim strYear As String
Dim strMonth As String
Dim dtm As Date
Dim dtmRef As Date
strDate = Trim$(SerialNr)
dtmRef = CDate(DateRef)
If Len(strDate) = 9 Then
strYear = Mid$(strDate, 2, 2)
strMonth = CStr(Asc(strDate) - 64)
dtm = DateSerial(CInt("20" & strYear), CInt(strMonth), 1)
Lifetime = DateDiff("m", dtm, dtmRef)
ElseIf Len(strDate) = 12 Then
strYear = Mid$(strDate, 3, 2)
strMonth = Left$(strDate, 2)
dtm = DateSerial(CInt("20" & strYear), CInt(strMonth), 1)
Lifetime = DateDiff("m", dtm, dtmRef)
Else
Lifetime = CVErr(XlCVError.xlErrNA)
End If
Exit Function
ErrHandler:
Lifetime = CVErr(xlErrNum)
End Function