Dates according to locale format

Jan 11, 2011 at 9:47 AM

Hey. 

I'm having problem figuring out how to set the cell date to be displayed according to user locale .
I set the cell value to a DateTime,  and uses format to display it as date.

DateTime dt = DateTime.Now; 
currWS.Cells[1, 1].Value = dt;
currWS.Cells[1, 1].Style.Numberformat.Format = "dd/mm/yyyy hh:mm";

When I open the file in Excel it shows a date in the above format: "29/11/2010 12:49"
My current locale is English (United Kingdom)
When I change the locale to English (United States) it styays the same.
I would like it to change to "11/29/2010 12:49"

I tried to put * but it doesn't work.
How do I set the format to take locale format?

Thanks

Coordinator
Jan 11, 2011 at 4:45 PM

Hi,

Read the section in the FAQ about numberformats...

Numberformats:
Use culture independent number formats: dot (.) for decimal, comma (thousand).
The easiest way to check a format or a formula is to create an Excel file --> Do the formatting / Add the formulas --> Save it as XLSX ---> Rename the file *.zip. Extract the file and have a look at the number formats in the \xl\styles.xml file and formulas in \xl\worksheets\sheet#.xml.

Jan

Jan 11, 2011 at 6:33 PM

Hi Jan,

Did what you suggested with the Excel file.
The only thing that is related to the cell date value is in the styles.xml

numFmtId="22"

 

Is there a way to set the formatId to 22, I ask this because I see this property is readonly in Style.Numberformat.NumFmtID

Thanks

Coordinator
Jan 11, 2011 at 8:16 PM

Hi,

aha, you picked one of the build in formats. To set the format use the following table...

        /// <summary>
        /// Id for number format
        /// 
        /// Build in ID's
        /// 
        /// 0   General 
        /// 1   0 
        /// 2   0.00 
        /// 3   #,##0 
        /// 4   #,##0.00 
        /// 9   0% 
        /// 10  0.00% 
        /// 11  0.00E+00 
        /// 12  # ?/? 
        /// 13  # ??/?? 
        /// 14  mm-dd-yy 
        /// 15  d-mmm-yy 
        /// 16  d-mmm 
        /// 17  mmm-yy 
        /// 18  h:mm AM/PM 
        /// 19  h:mm:ss AM/PM 
        /// 20  h:mm 
        /// 21  h:mm:ss 
        /// 22  m/d/yy h:mm 
        /// 37  #,##0 ;(#,##0) 
        /// 38  #,##0 ;[Red](#,##0) 
        /// 39  #,##0.00;(#,##0.00) 
        /// 40  #,##0.00;[Red](#,##0.00) 
        /// 45  mm:ss 
        /// 46  [h]:mm:ss 
        /// 47  mmss.0 
        /// 48  ##0.0E+0 
        /// 49  @
        /// </summary>            

 

In this case set the format to m/d/yy h:mm

Jan

Jan 11, 2011 at 8:29 PM

If I do it this way:
currWS.Cells[1, 1].Style.Numberformat.Format = "m/d/yy h:mm";
It doesn't solve my problem since this fix the date format no matter what is the locale.

Is there a way to set the Id ?

Coordinator
Jan 12, 2011 at 6:04 AM

No, it's internal, use the formats in the table if you what to use them.

If I try your format I get this in the style.xml ...

<numFmt numFmtId="168" formatCode="dd\/mm\/yyyy\ hh:mm" /> 
This worked for me...
ws.Cells["A1"].Style.Numberformat.Format=@"dd\/mm\/yyyy\ hh:mm";
Jan
Jan 12, 2011 at 8:27 AM

Sorry , dosen't work for me.
It stays 12/01/2011 instead of 01/12/2011 even my locale is US
Btw, this is wrong for the cell display, on the upper textbox where the FX located, it is in the right format

Jan 12, 2011 at 12:14 PM

With a close help from Jan , it works with this format:
ws.Cells["A1"].Style.Numberformat.Format = @"m/d/yy h:mm";

Thanks