Code:
Public Function StatusCheck(BaseDate, rngRed, rngPM1, rngPM2, rngCalib, rngString)
If rngRed = "Red Tagged" Then
StatusCheck = "Red Taggged"
ElseIf (Validate(BaseDate, ">", rngPM1, "+365") And Validate(BaseDate, ">", rngPM2, "+365") And _
Validate(BaseDate, ">", rngString, "+365")) _
Or (Validate(BaseDate, "<", rngPM1, "+365") And Validate(BaseDate, ">", rngPM2, "+365")) _
Or (Validate(BaseDate, "<", rngPM1, "+365") And Validate(BaseDate, ">", rngPM2, "+365") And _
Validate(BaseDate, "<", rngString, "+365")) Then
StatusCheck = "PM2"
ElseIf (Validate(BaseDate, ">", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365")) _
Or (Validate(BaseDate, ">", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365") And _
Validate(BaseDate, "<", rngCalib, "+30")) _
Or (Validate(BaseDate, ">", rngPM1, "+365") And Validate(BaseDate, ">", rngPM2, "+365") And _
Validate(BaseDate, "<", rngString, "+365")) _
Or (Validate(BaseDate, ">", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365") And _
Validate(BaseDate, ">", rngCalib, "+30") And Validate(BaseDate, "<", rngString, "+365")) Then
StatusCheck = "PM1"
ElseIf (Validate(BaseDate, "<", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365") And _
Validate(BaseDate, ">", rngCalib, "+30")) _
Or (Validate(BaseDate, "<", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365") And _
Validate(BaseDate, ">", rngCalib, "+30") And Validate(BaseDate, "<", rngString, "+365")) Then
StatusCheck = "Calib"
ElseIf (Validate(BaseDate, "<", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365") And _
Validate(BaseDate, "<", rngCalib, "+30") And Validate(rngString, "<", rngCalib, "")) Then
StatusCheck = "String Check"
ElseIf (Validate(BaseDate, "<", rngPM1, "+365") And Validate(BaseDate, "<", rngPM2, "+365") And _
Validate(BaseDate, "<", rngCalib, "+30") And Validate(rngString, ">=", rngCalib, "")) Then
StatusCheck = "Ready"
Else
StatusCheck = ""
End If
End Function
Private Function Validate(BaseDate, CompString, InputVal, InputAdd) As Boolean
If InputVal = "NA" Then
Validate = True
Else
Validate = Application.Evaluate(CLng(BaseDate) & CompString & "(" & CLng(InputVal) & InputAdd & ")")
End If
End Function
You use like so
Bookmarks