In this blog post, I’ll let you in on a date formatting trick.
I’m not talking about date formatting through Format Cells, but about the date format you enter manually when using the TEXT function. These date formats only work if the end user’s PC is set to the language the document was created in,
which can lead to problems in certain situations.
‘Mon 12 Jan 2019’
“Mon 12 jan 2019”
with Format Cells, they need to enter the following code:
‘ddd dd mmm yyyy’
A French user would use ‘jjjj jj mmm aaaa’ and a Dutch user would enter ‘ddd dd mmm jjjj’. When these users exchange a file, Excel automatically translates the used codes into the recipient’s language.
This is where Excel’s standard functionality ends:
dates aren’t automatically converted if a cell contains the ‘TEXT’ formula. That’s where this blog comes in.
If you need to format a date in a formula instead of using the standard formatting, here’s what you need to do:
Enter a date in cell A1
(Tip: CTRL; automatically enters today’s date.)
Enter the following formula in cell A2:
= TEXT (A1 ; “ddd dd mmm yyyy”)
You’ll immediately see where the problem at hand lies the formula only works if your device language is set to English. Excel won’t translate Y (English) to J (Dutch) or A (French) because it reads the code as text. You literally placed the code as text in the formula.
If your PC is set to Dutch, type = TEXT (A1 ; “ddd dd mmm jjjj”). If your PC is set to French, type = TEXT (A1 ; “jjj jj mmm aaaa”).
If the English user forwards their file to a French-speaking colleague, the date will be displayed as ‘Tue 01 Jan yyyy’. The French version doesn’t understand the code ‘Y’ and will think it’s just text that needs to appear after the date.
Luckily, you can solve this problem with a reference table. This table will help Excel recognise every known user language and link the language to the desired date format.
First, create a table that looks like this:
Enter a fixed date – in the example above, this date is in cell D2. The formatting of this cell isn’t important, but I prefer the number format without decimal places.
With this table, we’ll determine whether the user’s computer is set to English, Dutch or French.
- Enter the following formula in cell D4: =TEXT($D$2;”dd-mm-jjjj”)
- Enter the following formula in cell D5: =TEXT($D$2;”jj-mm-aaaa”)
- Enter the following formula in cell D6: =TEXT($D$2;”dd-mm-yyyy”)
You’ll see the following preliminary result:
The TEXT formula displays the date in the desired format, but the result is treated as text instead of a date. In the above example, the DateFormat column shows that the location of the user’s computer is set to Belgium or the Netherlands.
Since the output of these formulas is text, we can’t double-check their results with another formula.
To circumvent this, we’ll multiply the resulting date (or the 2 other, non-applicable cells) by 1. This will force Excel to show the result of the TEXT formula as a numeric value. Now you can set the cell formatting to ‘number’.
=TEXT($D$2;”dd-mm-jjjj”)*1
You’ll see that the two cells that don’t match the date format in the relevant language can’t be converted into numbers.
During our tests, we noticed that Excel doesn’t immediately detect the correct language format as soon as the file is opened, because Excel doesn’t execute any calculations upon opening a file. We’ll fix this by replacing the number 1 with ‘RANDBETWEEN(1,1)’. This forces Excel to calculate these cells upon opening the file.
The formula now looks as follows:
=TEXT(D2;”dd-mm-jj”)*RANDBETWEEN(1;1)
In the last column, we’ll enter the desired format for each language.
We’ll also add a formula in the cell E2 above this table.
The ‘Index’ formula analyses the blue zone that contains the desired formats. To determine which row we need, we’ll use the ‘Match’ formula to check which cell in the purple zone contains the same value as the fixed date in D2. The ‘Index’ formula now knows which row contains the desired solution, and it only refers to column 1 because we’ve only set one column as a base.
You can now enter the following formula in the cell A2:
= TEXT( A1 ; E2)
You can also give cell E2 a name; I named it ‘nFormat’. The formula then looks like this:
= TEXT( A1 ; nFormat)
To test it, you’ll need to close Excel and change your computer settings to another language or location. If you’re lucky enough to have a colleague nearby who speaks a different language, you can just skip this step and send them the file to check if it works.
