Here’s how I automate the “date cells not formatted the way I want them” situation, which is set up so I can use it to automate processing of future CSV files.
I build a separate sheet that is essentially simple direct references for all the original table cells into a new table, and the cells to which I want to do some reformatting, I apply the appropriate formula to that column or columns.
It’s usually a DATEVALUE using a LEN formula to extract dates that Numbers doesn’t recognize (the LEN formula trims off any unwanted characters).
I use this for CSVs downloaded form my online shops, mobile card swiper payment transactions, etc. If Numbers doesn’t recognize it as a date, it can mess up formulas that work on the data as dates (to extract months, years, etc).
In your case, you could use the DATEVALUE to just grab the date content from the original cell.
Then, in the future you just drop the CSV into the raw data table, and the reference table will automatically pull in all the data and reformat it for you.
Now as re-read your question, I am wondering if your question is more about the comma in the date messing up the data detection. And my other question for you is whether or not the brackets are something you added, or if they come in with the raw data.
If you need to remove the brackets, there will probably be some sort of use of multiple cleanup columns using the LEFT (and RIGHT) and LEN formulas with a -1 (to rid yourself of the start/end brackets). Assuming your malformed date is in cell B2, this form,a in C2 would get rid of the left bracket:
And then in D2, do similar using the LEFT formula, and change B2 to C2 in both spots.
Then in E2, run the DATEVALUE on D2
There’s probably a far more elegant way, perhaps using wildcards.