Excel Files with Custom Date Formats

The format string you pasted is an Excel display number format (it's actually an accounting format), and Power Query ignores cell display formatting entirely - it reads the underlying stored value. So the real issue is a mixed-type column: most cells are stored as text dates, but the one you re-typed by hand became a real Excel date, which is stored as a serial number. The column now mixes text and number, so a single "Change Type to Date" hits a value it can't parse and throws the [DataFormat.Error].

Handle both kinds in one step instead of a blanket type change. Add a custom column:

= if [DateCol] is number then Date.From([DateCol])

  else if [DateCol] is text then Date.FromText([DateCol], [Format="MM/dd/yyyy", Culture="en-US"])

  else null

Set that column to type Date and remove the original one. Date.From on a number reads it as the Excel date serial, and Date.FromText (with the right Culture) handles the text ones - so the 31 "corrected" files and the untouched files all parse, and it keeps working as new files arrive.

(Adjust the Format/Culture to match how the text dates actually look in your files.)