We help IT Professionals succeed at work.

on
High Priority
33 Views
I realized that I need an additional criteria added to the great solution I received yesterday on this forum. I've been trying to solve it myself but don't have the skillset yet to solve it.

I have attached a sample spreadsheet that shows the results I am looking for once the additional criteria is added.

If Column A = "AIP_ENR" or "AIP_AD" or AIP_GEN" then Expiry Issue = ""

The current solution's VBA code is as follows:

``````Function expiryIssue(rng As Range) As String
Application.Volatile
Dim dte1 As Range
Dim dte2 As Range
Dim status As String
Set dte1 = rng.Cells(1, 1)
Set dte2 = rng.Cells(1, 2)
status = rng.Cells(1, 3)
If status <> "Expired" And status <> "Cancelled" And status <> "Replaced" Then
' If there is no Proposed or Approved expiry date
If dte2 = "" And dte1 = "" Then
expiryIssue = "Missing expiry dates"
Else
'If there is no Approved Expiry date and the Proposed Expiry date is within 60 days of todays date
If dte2 = "" Then
If dte1 < Date + 60 Then expiryIssue = "Review proposed expiry date"
Else
'If the Approved Expiry date is older than today's date
If dte1 = "" Then
If dte2 < Date Then expiryIssue = "Issue"
Else
If dte2 < Date Then expiryIssue = "Issue"
End If
End If
End If
End If
End Function
``````

Thanks!
Andrea
EE_Expiry_Exclude_Sections.xlsm
Comment
Watch Question

## View Solution Only

Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
``````Function expiryIssue(rng As Range) As String
Application.Volatile
Dim dte1 As Range
Dim dte2 As Range
Dim status As String
Set dte1 = rng.Cells(1, 1)
Set dte2 = rng.Cells(1, 2)
status = rng.Cells(1, 3)
Select Case Cells(rng.Row, "A")
expiryIssue = ""
Exit Function
End Select
If status <> "Expired" And status <> "Cancelled" And status <> "Replaced" Then
' If there is no Proposed or Approved expiry date
If dte2 = "" And dte1 = "" Then
expiryIssue = "Missing expiry dates"
Else
'If there is no Approved Expiry date and the Proposed Expiry date is within 60 days of todays date
If dte2 = "" Then
If dte1 < Date + 60 Then expiryIssue = "Review proposed expiry date"
Else
'If the Approved Expiry date is older than today's date
If dte1 = "" Then
If dte2 < Date Then expiryIssue = "Issue"
Else
If dte2 < Date Then expiryIssue = "Issue"
End If
End If
End If
End If
End Function
``````

Commented:
Thanks, Martin, for a quick and perfect solution!

Cheers,
Andrea
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
Experts Exchange Top Expert VBA 2018, 2019
Experts Exchange Distinguished Expert in Excel 2018