Excel - How to convert US date string (dd-mmm-yyyy) to European date-CodePudding

How can date in the US format dd-mmm-yyyy be converted to a date in European format.

When importing text based data with US Dates, some dates will import as a date, some import as a string.

A US date like 11-Jun-2021 is equal to NL format, so it will import as a date. A US date like 11-Oct-2021 should be converted to 10-okt-2021, but it's imported as a string.

Is there a way to convert this string to a date format?

CodePudding user response：

Found something. It's kinda dirty, but it works:

=IF(C3=0;"";IF(IFERROR(FIND("Oct";C3;1);FALSE);REPLACE(C3;FIND("Oct";C3;1);3;"Okt");IF(IFERROR(FIND("May";C3;1);FALSE);REPLACE(C3;FIND("May";C3;1);3;"Mei");IF(IFERROR(FIND("Mar";C3;1);FALSE);REPLACE(C3;FIND("Mar";C3;1);3;"Mrt");C3))))