none
How to format Excel dates for multiple cultures RRS feed

  • Question

  • We are using C# to format the date in an excel worksheet. We get the format using CultureInfo.CurrentCulture.DateTimeFormat to know for example if the date should be dd/mm/yyyy or mm/dd/yyyy, and set it to the number format which works fine in most languages. However, France for example uses jj/mm/aaaa instead of dd/mm/yyyy in Excel date formatting. So if you try to use the letters 'd' and 'y' to format the date it doesn't work. You end up with 03/dd/yyyy, so the month formats correctly but not the day or year.

    So how can I get number format to work with different localizations? I can't seem to find anywhere that tells you what letter is used for a localizations m, d, or y.

    Edit: we want to format it like this YYYY-MM-DD HH:MM:SS.sss, but with the cultures date format.
    Tuesday, March 31, 2015 3:43 PM

All replies

  • Creating Excel Solutions for Use in Multiple Countries/Regions Using Visual Studio Tools for Office

    Mark as answer or vote as helpful if you find it useful | Ammar Zaied [MCP]

    Tuesday, March 31, 2015 4:18 PM
  • If you are using a custom format specifier then localization is pretty much irrelevant.  The whole purpose of a custom format is because you don't care about the localized value.  If you want YYY-MM-DD then you are overriding the localized format, whatever that is.  Converting from a DateTime to a string using a custom formatter will work using the standard format specifiers. This has nothing to do with localization nor would the localization have any impact on the results.  YYYY-MM-DD will format the value as year-month-day always, without exception.

    It sounds like the issue you're having is that you're trying to load your formatted string value into Excel.  But in that case you're actually passing Excel a string, not a date.  Excel will try to apply whatever column formatting was specified to the string value. If you want Excel to be responsible for formatting the value then pass the date value itself, not the ToString() equivalent.  Excel will then be responsible for formatting the value according to the formatting rules of the column.

    If you are trying to go the other way and get a DateTime from a string then you'll be using Parse/TryParse. In that case it will use the culture settings to parse or you can be explicit about the formatting when you're using a generic format.

    This really sounds like a problem more related to how you're using Excel so I'm going to move it to that forum.

    Michael Taylor
    http://blogs.msmvps.com/p3net

    Tuesday, March 31, 2015 5:29 PM
  • We are passing a date into the individual cells, but trying to set the format for the entire column so Excel formats the passed date, like this.

    range.EntireColumn.NumberFormat = "dd/MM/yyyy HH:mm:ss"

    At the top of the column we want how it is formatted labeled as a string (like "dd/MM/yyyy HH:mm:ss") so the when looking at the cells you know how they are formatted.

    But it seems like for French for example we would need to pass

    range.EntireColumn.NumberFormat = "jj/MM/aaaa HH:mm:ss"

    in order for it to know how to format the date correctly. Manually changing it like this works, I just can't figure out if there is a way to know if a localization needs a different character for formatting, or if there is a different way to do this. Mostly we want the time shown in more detail, since without changing the number format it only shows down to the minute by default.

    Tuesday, March 31, 2015 5:46 PM
  • There is no such format specifier as jj or aaa in .NET. Excel uses .NET's String.Format so it wouldn't work there either. I'm not sure where you're getting that formatting from.  

    The format specifier you gave will work for any culture, French or otherwise.  You are basically saying that whatever language is being used display a 2 digit day followed by a 2 digit month and 4 digit year.  It doesn't matter the language.  Are you specifically seeing an issue with French?  If so then what is it showing that is incorrect?

    Tuesday, March 31, 2015 6:06 PM
  • Here is a picture (sorry it says I can't post a picture yet) from within the French excel. The left column has the custom format jj/mm/aaaa hh:mm:ss, and gets formatted fine.

    When trying to change the column on the right to dd/mm/yyyy hh:mm:ss it pops up with the error:

    "Microsoft Excel cannot use the number format you typed. Try using one of the build-in number formats. "

    In the custom formats it shows different combinations of jj-mm-aa, jj-mmm, etc. This is the French version of excel installed separately along side the English one. If I change my windows region back to English and open excel, the dd/mm/yyyy hh:mm:ss works fine and the custom formats shows things like mm-dd-yy, etc.  Trying to use jj/mm/aaaa hh:mm:ss will give me that same error.


    Tuesday, March 31, 2015 6:28 PM
  • This sounds like an Excel problem to me. The format you gave isn't a valid .NET format so why it is showing that is odd. I opened Excel and went to the formatting options for Date. I then switched to the French (France) language and I saw different (custom Excel) formatting strings but none of them contained what you are seeing.

    I think at this point I'll let someone with Excel expertise chime in on what is going on.  I've never seen this behavior before and I'm not sure how it is working for you.

    Tuesday, March 31, 2015 6:52 PM
  • Is quite an old argument, I was struggling using an italian version of Excel, I used the XlApplicationInternational enumeration , I need to set a range numberformat like this "dd MMMM yyyy" but in italian I have to write like "gg mmmm aaaa", the problem is that my customers are using italian, german and english version of Excel, so I built a custom version of the string (I use VB.net, for C# is easy to translate):

    Dim myxlapp As New Excel.Application

    Dim myd As String = myxlapp.International(Excel.XlApplicationInternational.xlDayCode)
    Dim mym As String = myxlapp.International(Excel.XlApplicationInternational.xlMonthCode)
    Dim myY As String = myxlapp.International(Excel.XlApplicationInternational.xlYearCode)

    Dim mydateformat As String = myd & myd & " " & mym & mym & mym & mym & " " & myY & myY & myY & myY

    then I apply the new format to my range:

    myRange.NumberFormat = "[$-809]" & mydateformat

    (the $-809 is because I want to obtain the date in english language also if I'm using an italian or german regionale setting, reference here: https://msdn.microsoft.com/en-us/library/dd318693(VS.85).aspx and here https://www.science.co.il/language/Locale-codes.php)

    Ciao


    Wednesday, April 25, 2018 9:48 AM