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

Jack Parry

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?

Paul Newcome

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))

Paul Newcome

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))

Jack Parry

@Paul Newcome Works perfectly! Thanks again Paul :)

Paul Newcome

Happy to help. 👍️