Excel Date Formats: Solving Ambiguity Problems

[nomadreid]

TL;DR Summary

I want my dates to appear in the format dd/mm/yyyy, so I set the cell to Category: Date, Type: English (United Kingdom). I type in 3/4/2019 (3 April 2019), and Excel converts it to 04/03/2019. What is going on?

The summary is complete to describe the problem, but to forestall the solution to either (a) leave the Category : "number" and just type what I want, or (b) put the Type as "English(United States)" and type 3/4/2019, I mention why these solutions would create difficulties: a person to whom I will send the spreadsheet will then link the cells to another spread sheet in order to determine data for that day, and an ambiguity would cause problems.

 

[Mark44]

Summary:: I want my dates to appear in the format dd/mm/yyyy, so I set the cell to Category: Date, Type: English (United Kingdom). I type in 3/4/2019 (3 April 2019), and Excel converts it to 04/03/2019. What is going on?

The summary is complete to describe the problem, but to forestall the solution to either (a) leave the Category : "number" and just type what I want, or (b) put the Type as "English(United States)" and type 3/4/2019, I mention why these solutions would create difficulties: a person to whom I will send the spreadsheet will then link the cells to another spread sheet in order to determine data for that day, and an ambiguity would cause problems.

Excel is formatting the date according to the locale. With English (United Kingdom) as the locale, it displays the date in mm/dd/yyyy format. If you change the locale to English(United States), the same date will be displayed in dd/mm/yyyy format.

 

[nomadreid]

Thanks, Mark44. I can do that, but I am curious: since the British use dd/mm/yyyy in everyday life, and the Americans use mm/dd/yyyy in everyday life, why does Excel switch these?

 

[pbuk]

why does Excel switch these?

Excel stores the date internally in a universal format. By default it will use the user's "international settings" (essentially the equivalent of a "locale" in Unix-like systems) to display the date to a user in the format he expects. To override this you can explicitly set the format of the cell to dd/mm/yyyy or mm/dd/yyyy using Cells -> Format -> Format Cells -> Number. The options shown under Date that are prefixed with a * are taken from your international settings and may display differently on another PC, the ones that have no * should be the same anywhere so select dd/mm/yyyy (or better still yyyy-mm-dd) if you want everyone to see the same thing.

 

[nomadreid]

Thanks, pbuk.

 

[nomadreid]

xAh, apparently there is more to it: I tried the following experiment. I chose the language of the country I am in (which uses dd/mm/yyyy), selected a dd/mm/yyyy from the menu choices, and put in 23/05/2019. That works. But then (in the same box, without changing the settings) I changed the "23" to "11" so that I would expect 11/05/2019 (11 May 2019 as desired) but EXCEL automatically switched it to 05/11/2019 (which then reads as 05 November, which I don't want) -- although it did not change my settings. That is, if it can switch it to the American format (since 23 can't be a month, but 11 can), it will. How can I override this?

To compound the weirdness, the date 11 May 2019 stays as I typed in, 11/05/2019, in the field up at the top (where fx appears to the left), but is switched in the cell.

 

[anorlunda]

I sympathize with the Excel authors. Over the years there have been many "bugs" reported that turn out to be just violations of personal preference. If you think Excel behaves in a non-intuitive way, there's a good chance that other people think it is intuitive.

There is no single answer that makes all people happy. A menu asking you to choose your preference on 300 questions is not welcome either. Perhaps selecting country, is as close as they can get.

 

[pbuk]

xAh, apparently there is more to it: I tried the following experiment. I chose the language of the country I am in (which uses dd/mm/yyyy), selected a dd/mm/yyyy from the menu choices, and put in 23/05/2019. That works. But then (in the same box, without changing the settings) I changed the "23" to "11" so that I would expect 11/05/2019 (11 May 2019 as desired) but EXCEL automatically switched it to 05/11/2019 (which then reads as 05 November, which I don't want) -- although it did not change my settings. That is, if it can switch it to the American format (since 23 can't be a month, but 11 can), it will. How can I override this?

To compound the weirdness, the date 11 May 2019 stays as I typed in, 11/05/2019, in the field up at the top (where fx appears to the left), but is switched in the cell.

I'm not sure if you can override this behaviour, this is Excel trying to be 'helpful', and it gets even stranger than that: type 5/23 into cells with various different formats. Sometimes it will guess 23 May 2020 and sometimes 1 May 2023!

In practice this is less of a problem - always use an unambiguous display format (I use either d mmm yyyy or yyyy-mm-dd), and if you are relying on user input that may not be checked, use separate cells for day, month and year and combine them in a formula.

 

[nomadreid]

Thanks for the interesting suggestions, pbuk.

I don't quite see how the d mmm yyyy suggestion would work if d>9, though.

The separate cell idea is one I will use when I rely on user input (not the case that I am dealing with now, but in the future...)

Another alternative that I might end up using is to bow to this "helpful" insidious Americanization, use the imposed format, put a large warning at the beginning of the spread sheet that all dates are of this format, and let the other users handle the hassle when they link with a reference that uses a more standard format. Not ideal, but I may follow the path of least resistance ("Resistance is futile") if I don't find a way to override this "helpful" behaviour. Ugh.

 

[pbuk]

I don't quite see how the d mmm yyyy suggestion would work if d>9, though.

It displays 10 as 10 and 9 as 9 rather than 09 - works for m too, and also h, m and s (with m now meaning minutes - how many problems does that ambiguity cause!)

One final suggestion: if you use =TEXT(a1, "dd/mm/yy") it should ignore any internationaltrickery, you will have to use a separate cell to store the value though.

 

[nomadreid]

Thanks for the great suggestions, pbuk, and also thanks for the explanations.

In the meantime I settled on the following solution (but will keep your suggestions in mind for the future, or if my present solution goes "splat" upon arrival): I found that if I select "English (United States)", then it let's me type in whatever I want without interfering (except that it will switch "09/02/2019" to "9/2/2019 -- although it will keep "12/02/2019"as is. The pattern is clear, even though annoying.)

Of course if the receiver were to ask EXCEL to convert it to a format that names which number represents the month, then the wrong result would come out, so I just have to caution the receiver about this if he tries to manipulate the entries. However, merely printed out or viewed on the screen, the spread sheet now shows the desired format (if I am not fussy about zeros).