How can we improve Excel for Windows (Desktop Application)?

Formatting codes need to be translated inside formulas

The formatting codes are different in every language, so for example, for the day is "d" in English, "g" (from "giorno") in Italian, "T" (from "Tag") in German).

Working in a multi-language environment where different users have different language settings on their PC gives the problem that when you write a formula that contains the formatting codes, the formatting codes are not translated and the formula doesn't provide the intended result.

For example, if I, with language settings in Italian, want to write a formula to display the current date as text in English, I have to use the following: ="Today is: "& TEXT(TODAY(); "[$-0809]gggg"). This formula on a computer with different language settings (English, German or whatever) will result in "Today is: gggg".

So, my suggestion is that the formatting codes in the formulas are automatically translated like the names of functions and all the menus/ribbon items.

Alternatively, could be useful that the formatting codes for English can be used also with different languages, so I can use ="Today is: "& TEXT(TODAY(); "[$-0809]dddd") even with my Italian settings and the formula will give the correct result "Today is Thursday" independently from the language settings of the computer.

40 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Franz Verga shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    7 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Franz Verga commented  ·   ·  Flag as inappropriate

        @Roy: I know that we are talking about the same stuff...
        The point is that, at the moment, the formatting codes are not according the language settings in Office (Excel), but according to the Windows settings... So, because I have Italian settings for place and time in Windows, I have to use in Office (Excel), the Italian formatting codes, otherwise the formula doesn't work. When a colleague of mine with his/her German settings (I live and work in Germany...) opens my file, he/she will see a wrong result, tipically gggg, in the case of the formula of my original post....

      • Roy commented  ·   ·  Flag as inappropriate

        @Franz: Sorry, one last clarification. If your colleague then changed the code, perhaps to "TTT", he would type precisely that, T-T-T, and Excel would store it internally (using my example below) as "uuu" and when he sent it back to you, you would never see either of those: it would show as "ddd" to you. Each of you is using abbreviations that make sense to you (or presumably you'd've chosen a different language version of Excel). So each of you USE and SEE logical language-appropriate codes while Excel really stores it however makes best sense to Microsoft. Everybody wins.

      • Roy commented  ·   ·  Flag as inappropriate

        @Franz: Then we ARE talking about the same thing. I am not predicating anything on the Windows language version or settings. Solely the language used in the user's copy of Excel. So YOU use an English language version and use a date code of literally "dddd" which makes perfect sense to you because "d" = "day" fits your English language expectations. It works perfectly. You send it to a colleague who uses a German language version of Excel because, presumably, he prefers German. What HE sees when he looks at your date code is "TTTT" (i.e.: he does NOT see the "dddd" you typed in). That makes immediate sense to him and eases his understanding of the code and happiness with the spreadsheet. What neither of you know, or care about, is that when you typed that "dddd" in your spreadsheet, Excel really saved it as, say, "uuuu". But that is completely INTERNAL to Excel: neither of you will ever see "uuuu". YOU see it rendered for English Excell as "dddd" and your colleague sees it rendered for German Excel as "TTTT". Notice that this is utterly independent of your Windows language version/settings: even though your Windows is in Italian and uses Italian date settings, you will NEVER see "gggg" because this only involves Excel and ITS settings, not the Windows language and settings. All done here though.

      • Franz Verga commented  ·   ·  Flag as inappropriate

        @Roy: I'm using Excel in English, but in Windows I have the Italian language settings, so, the formatting codes I see in Office (Excel) are the Italian ones: when I create a formula that uses the formatting codes, I am forced to use the Italian ones, no choice.
        The point is that when I share my file with colleagues that have different language settings in Windows (English, German, Spanish, it doesn't matter), the formatting codes inside the formula will not be recognized and the formula will show only the formatting codes...
        This is the background of my proposal.
        A the very end, it's not that important if my colleagues see the formatting codes in the language they have set the preferences for time in Windows.
        For me it's important that the formula works properly, not depending on the language preferences...

      • Roy commented  ·   ·  Flag as inappropriate

        @Franz: Yes, the operating system does impact how things come up, etc. But I thought you were looking for the actual formulas used by people to show things in the formula using language appropriate symbols. I use English Excel and would not find using "T" in any way intuitive the way I do find using "d" for "day." But more so, if I share (or create for) a worksheet with a, say, German individual or company, I will desperately want to use "d" but will equally desperately want the German user to see "T" when using or evaluating the spreadsheet. If Excel used some underlying code that we never see, it could present "d" to me (opening it with English Excel) yet, without missing a beat or making any change at all, present "T" to the German user using his German Excel. But it looks like I missed your point in the suggestion. Still, I'd like to see it so work can be directly shared much more easily. And a worker in, say, Malaysia, could create a useful spreadsheet for me without ever learning more English than needed for negotiating the contract to produce it.

      • Franz Verga commented  ·   ·  Flag as inappropriate

        @Roy: I think that something like that already happens, because when you want to format a date, for example, the formatting codes are already translated accordingly to the language settings of Windows (not of Office), but this translation doesn't happen in formulas, so opening a file where such a formula was written with different Windows language settings will give the problem...

      • Roy commented  ·   ·  Flag as inappropriate

        It seems like a simple abstraction. Excel would use a set of codes only it has access to for the actual saved version of any such format. Excel would (does?) check for language default when starting (could offer options here) and show codes in the formulas displayed according to the language appropriate abbreviations. For instance, the "gggg" displayed to the Italian default language user, and used by that user, but the actual saved formula would be whatever (say "!!!!" for all it matters. A German user might see "TTTT" while I might see "dddd". In fact, ALL language related matters in formulas could be done this way. Or if they already are, then this could be slipped into that functionality.

      Feedback and Knowledge Base