Thursday, August 30, 2018

xls - 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?

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?

No comments:

Post a Comment

hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...