Code Not Identifying Yesterday As Delinquent

Hello All,

The purpose of the below code is to identify if a shipment is delinquent or not. The intent of the code is to look at specific date and if that date is in the past then the shipment is delinquent. If that date is in the future it says whether it is next month or the month after, etc.

However, I have run into a snag. It seems to only be looking at the month. I need it to look at the day so that I know a date of yesterday is delinquent if it hasn't shipped.

With .Range(.Cells(2, "N"), .Cells(.Rows.Count, "N").End(xlUp))
    .Offset(, 1).Value = .Parent.Evaluate _
           ("IF(ISNUMBER(" & .Address & ")," & _
                "LOOKUP(" & _
                   "(TEXT(" & .Address & ",""yyyy"")*12+TEXT(" & .Address & ",""mm""))" & _
                        "-" & _
                    "(TEXT(TODAY(),""yyyy"")*12+TEXT(TODAY(),""mm""))," & _
               "{-9.99E+307,0,1,2,3}," & _
          "{""Past Due"",""Current Month"",""CM+1"",""CM+2"",""Beyond""}" & _ ")" & _
                    ","""")")
   End With
End With

Any help with this code is greatly appreciated.

The original posting that helped me with this can be found here

Thank you.

Kelly

The original suggestion satisfied the original criteria I believe.

It seems you're now saying that the revised criteria is such that :

• "Past Due": any Date Prior to Today
  
• "Current Month": any Date between Today and end of Current Month
  
• Remaining output as before

If the above is indeed the case I would say that given the variance in units of comparison it would be simplest to add a pre-emptive IF to handle prior dates

Sub Example()
    With Sheets("Sheet1")
        With .Range(.Cells(2, "N"), .Cells(.Rows.Count, "N").End(xlUp))
            .Offset(, 1).Value = .Parent.Evaluate _
                   ("IF(ISNUMBER(" & .Address & ")," & _
                        "IF(" & .Address & "<TODAY()," & _
                            """Past Due""," & _
                            "LOOKUP(" & _
                                "(TEXT(" & .Address & ",""yyyy"")*12+TEXT(" & .Address & ",""mm""))" & _
                                    "-" & _
                                "(TEXT(TODAY(),""yyyy"")*12+TEXT(TODAY(),""mm""))," & _
                                "{0,1,2,3}," & _
                                "{""Current Month"",""CM+1"",""CM+2"",""Beyond""}" & _
                            ")" & _
                        ")" & _
                    ","""")")
        End With
    End With
End Sub