Some time ago, I got a mysterious bug report from Mehmet, a management consultant in Turkey, about the Gantt Chart Excel Spreadsheet I’d made. I wasn’t able to reproduce the bug until I set my locale to Turkey, and even then I couldn’t figure out what was triggering the problem. Mehmet turned to his local Excel gurus, and then sent me an update email about how he’d fixed it. At the time I didn’t quite understand what he’d done, until I realized that Excel’s Text() function, which is used heavily to convert dates to the exact bits of text I need, was not locale-independent.

For example, say we have the date August 12, 2011 stored in cell A1. I can use the built-in Excel function Text() to display the “Day of the Week” in cell B2 as follows:

    =Text(A1,"dddd")

This displays “Friday”. However, the "dddd" string is English-specific; the online English documentation doesn’t mention that this string changes for different languages. The convention “d for day” makes sense in English, but in Turkish it makes sense to use “g for gün”; not surprisingly, for Turkish Excel the function would have to be rewritten as:

    =Text(A1,"gggg")

This has proven to be a broader problem as reported on the blog, and thanks to Mehmet I can finally fix it. I’ve added a “date translation string” table to version 4 of the spreadsheet, so you can localize the date strings without having to adjust the formulas directly.

If you’re using a non-English locale, you might want to revisit the Gantt Chart Excel Spreadsheet page and download the latest version. I’ve updated the links.

On a side note, the reason this possibility didn’t occur to me was that I didn’t expect the parameters for a function like this to change from locale to locale, because from a programming perspective this would just make things harder (from my English-centric world view, anyway). To test my assumption, I just checked the PHP date format page and switched to the Turkish version to see if the format codes change; they don’t seem to. Excel, however, is targeted at everyday users operating in their own local context, so changing the date codes per locale makes a kind of sense. Unless you are an international organization, of course.