Using INDEX/MATCH to pull a date and CountIF not recognizing as a date

Options

Hi, I'm using INDEX/MATCH to pull a date from one report into another based on the same ID number.

If there is no match, an error message populates.

=IFERROR(INDEX({Corporate CAPA Closed Effective Date}, MATCH([InfoCard Number]@row, {Corporate CAPA Closed InfoCard Number}, 0)), "CAPA Not Closed")

Once the formula is inputted and the column formula is set. The column is switched to a date column.

I then have a sheet that I use to calculate the CAPAs completed for that month.

I want to use the column ('Effective Date') from the Index/Match, but its giving me #INVALID DATA TYPE

=COUNTIFS({All Sites CAPA}, CONTAINS("CAPA", @cell), {All Sites CAPA Effective Date}, AND(MONTH(@cell) = 6), {All Sites CAPA Closed On Time?}, CONTAINS("CAPA Overdue", @cell))

I'm guessing its the fact I'm using the INDEX/MATCH, so it doesn't recognize it as a true date. Is there a way around this?

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    The INDEX function should be retaining the data type (in this case date value). It sounds like the issue is that there is at least one blank or no-date value within the range. To accommodate that you would use:


    =COUNTIFS({All Sites CAPA}, CONTAINS("CAPA", @cell), {All Sites CAPA Effective Date}, IFERROR(MONTH(@cell), 0) = 6, {All Sites CAPA Closed On Time?}, CONTAINS("CAPA Overdue", @cell))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!