We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
close icon

Localizable Date Format

I see that it is possible to specify date formats by storing the numeric representation of the date in the sheet.Range[CellNo].Number member and a format string in the sheet.Range[CellNo].NumberFormat field. The problem is, specifying a format here (say, "m/d/yyyy h:mm") is not localizeable.

Within Excel, if you format a cell as Category: Date, and then select one of the few Types that are localizeable (denoted with a '*' as seen in the attached screenshot), the spreadsheet will display the date in that general format, but localized. That is to say, it will display as “3/9/2007” on an American English machine, and “9/3/2007” on a Spanish machine.

Is it possible to specify a cell as a Date cell so that Excel will automatically format it and allow for localization? If not, is there a workaround for localization?


dateformat.zip

7 Replies

AN Anonymous August 27, 2007 10:19 AM UTC

you can do it by setting number format to the following thing
range.NumberFormat = @"[$-F800]dddd\,\ mmmm\ dd\,\ yyyy";
And you will get date number format that will depend on you current culture settings.
[$-F800] is important here, it looks like the rest is ignored by MS Excel.


SD Sarathi D Syncfusion Team August 30, 2007 12:44 AM UTC

Hi Ami,

Thank you for your information. Please let me know if you have any queries.

Best Regards,
Sarathi


BH Brian Hardie September 7, 2007 10:23 PM UTC

This is great, but not quite right. As you mentioned, changing the later part of the format string has no effect. What I'm really interested in is displaying the date numerically (9/7/2007) without the alpha names (Friday, September, etc.).


SD Sarathi D Syncfusion Team September 8, 2007 01:57 AM UTC

Hi Brian,

We are currently looking into this issue. I will update you the status of the issue within two business days.

Sorry for the inconvenience caused.

Regards,
Sarathi


AN Anonymous September 10, 2007 08:49 AM UTC

There are two ways to do it:
1) range.NumberFormat = "m/d/yyyy";
2) range.CellStyle.NumberFormatIndex = 14;

They do the same operation and there shouldn't be any difference between them


CM Christian Muirhead December 5, 2007 12:45 PM UTC

We're also seeing this problem.

If we create an xls file containing dates, and load it on a machine where the regional settings are en-UK (so they should be dd/mm/yyyy), we get the dates displayed in mm/dd/yyyy format (although they are correct - MONTH and DAY return the correct results).

Looking at the date cell's formatting, we see it has a custom format of mm/dd/yyyy. When creating the file, we could override this, but that would mean that we were specifying the date format using the regional setting when the file is created. We want the date to be displayed correctly on the machine that opens the file.

Is there a way we can get this?

Thanks,
Christian



MW Melba Winshia Syncfusion Team December 14, 2007 02:48 PM UTC

Hi Christian,

I regret for the delayed response.

I regret to let you know that currently we do not provide support to extract any formatting information from the user's culture. So you must specify the number format as "dd/mm/yyyy" if you want to display in dd/mm/yyyy format while creating the file itself. Sorry for the inconvenience caused.

Please let me know if you have any other questions.

Thanks,
Melba



Loader.
Live Chat Icon For mobile
Up arrow icon