Contains formula with Wildcards

[alexcellings]

Hi All, 

I searched the forums for this issue and did not find any other specific matches for my scenario so hopefully someone has some ideas. My raw data looks like the below example table with dates listed across 12 total columns in descending order. I need to find all instances where a date occurs after 11/1/2021 AND the cell to the right is a "0".

Date1	Date2	Date3
20220402	20210509	0
20211105	0	0

The solution I tried was creating a string field called "Joined dates" in which all of the 12 date columns (stored as string fields in the YYYYmmDD format) are joined together and separated by a pipe (|). An example of the above two rows would look like this:

Record 1: 20220402|20210509|0

Record 2: 20211105|0|0

I then decided to filter each row for a date pattern of >/= 11/1/2021 AND a "0" as the next date. So using my two example records above, it should only return the second record.

Current Solution: To accomplish this, I'm currently using the formula Contains[joined dates],"202111??|0") however I am getting no results once I add in the wildcards. I've tried every wildcard I think of (*,?,%). The formula works fine if I just leave it as "202111" but I need that subsequent record to be "0".

Any thoughts here on why the wildcards would be causing no results to show up?

[DataNath · Accepted solution]

@alexcellings managed to get this working for your 2 example rows and the date logic ought to hold up as the number will only get bigger in that format. Let me know if you run into any issues when applying this to a wider dataset though.

[joelmiller66 · Accepted solution]

@alexcellings 

Hope this helps. The key to is update the fields to be dates, transpose and use the Multi-Row to check.

I attached a sample.

[alexcellings]

This community is amazing. I ended up using this one so marking this as the solution. Thanks!