Dutch Excel displaying dates in different formats even though they are in the same format in the file - is there a way to ensure consistency?

Question

We create files with dates in them in the form dd/mmm/yyyy. These spreadsheet files are in html format with extension .xls (Further info: created via HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";)

When I load one of these files into Excel 2007 on my Win7 machine the dates all appear as dd-mmm-yy (e.g. "28-Jun-2010") - and this is the same behaviour as when we load the files into older Excel programs also localised English.

When this same file is loaded into Excel 2002 NL on XP that is localised Dutch (and that I do not have access to) some dates appear as dd-mmm-yy but other dates appear as dd/Mmm/yyyy.

This seems to be down to the month element of the date:

Months with "Oct", "Mar" or "May" in them appear as dd/Mmm/yyyy (e.g. "30/Mar/2010") other months appear as dd-mmm-yy (e.g. "28-jun-10")

When I look at the cell format info the cells I get "custom"/"dd-mmm-yy" for cells that appear as dd-mmm-yy and "General"/"no specific number format" for those that show as dd/Mmm/yyyy.

Why those particular months? I assume down to localisation? Is there a way to get all the dates displayed consistently without having to manually alter the tables?

Is there a setting in Excel NL to do this?

Update: Just checked - May, March, & October are the three months where the short three-letter form of the month does not match in both English and Dutch (the other months do.) Therefore this is why these dates are not being picked up as dates.

Is there a way to enable Excel to pick these up as dates - even though the short form does not match the English?

Answer 1

To the question: "Why those particular months?"

All the other abbreviations of months are the same in Dutch as they are in English.

Jan  Jan
Feb  Feb
Mar  Maa <--
Apr  Apr
May  Mei <--
Jun  Jun
Jul  Jul
Aug  Aug
Sep  Sep
Oct  Okt <--
Nov  Nov
Dec  Dec

As you can see, only March, May and October are different from dutch when abbreviated.

Answer 2

Late answer, but for anyone that still searches for a solution: I ran into a similar problem when trying to convert texts like "ma 02 okt 2017 17:00" or "vr 02 mrt 2018 19:00" into a date. Excel's datevalue function is language specific, so I had to replace the texts " okt " into "/10/", " mrt " into "/03/" and " mei " into "/05/". For all other months there was no difference between English and Dutch settings, so no replacing texts with numbers was needed. Complete formula:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(G114;3;12);" okt ";"/10/");" mrt ";"/03/");" mei ";"/05/"))