I'm trying to format dates given to me in an excel spreadsheet, and some of the values are seen as a text. I've tried using the format option to set a date and time to the entire column.

The Sheet

CodePudding user response：

Try

={""; ARRAYFORMULA(iferror(IF(A2:A="",,DATE(YEAR(E2:E), MONTH(E2:E), 1)),date("20" & REGEXEXTRACT(E2:E,".*\/(\d{2}) "),REGEXEXTRACT(E2:E,"\d "),1)))}

Reference

REGEXEXTRACT