Recognising months given in a local language

While reading the contents of a text file in VBA, I came across a date “31/OCT/2023”. The language of the report is recognised (form example, sometimes also 28/FEV/2023 for French reports)

I know that I can manipulate the date in local language from a date perspective using a worksheet function TEXT. For example, I can change the string “31/OKT/2023” (Dutch) to 31 October 2023
(English) using

Please Login or Register  to view this content.

.

However, due to my language settings Excel does not recognise “OCT” as a valid month. DateValue(“31/OCT/2023”) gives an error (invalid type).
I can off course create a custom function that includes all months for the report languages I process, but I was wondering if Excel or VBA also provided a standard function or method to a reverse lookup of a date string that is provided in another language (which is recognised based on other strings in the source report) to the language setting of Excel.

Anyone has an idea?

Art

I know this, but this isn't solving my issue. The problem is that my source string is not recognised by Excel because it contains a month value in a language that is not the same as the language setting in my excel version (for example "OCT" or "MAY"). Thus, before Excel can process this as part of a date (month) I should be able to include in which language the month is provided. Hence my question, because this is a reverse solution as the objective of the "TEXT" formula.

You could create a LAMBDA function (DateTranslator) to translate dates.

DateTranslator

Formula:

Please Login or Register  to view this content.

Could do the same with macro, but some places you can't use it.