I’m seeking a way to get dates in a custom format, by default
eg enter “3 May 2020” and I then currently see “03/05/20” note en_GB ordering.
I’ve tried setting a template to display only “Date” as ISO 8601 date format, that is YYYY-MM-DD but it then applies to even numbers, type 1 in a cell and I get 1900-01-01
I looked but cannot find a way to edit a locale default, or make a new locale. Has anyone found a reliable and consistent solution?
You need to distinguish date recognition patterns and the default date format applied in a cell if a date input was recognized or DATE is the preset result type of a formula.
The default date format is defined by the locale and can’t be changed by the user.
Fortunately there are some locales based on the English language, but having different defaults concerning currency values (don’t use!) and dates.
I’m a German living in Germany but use locale English (Canada) because its English is roughly GB, and its deault date format is ISO 8601. For my profiles I added D.M.. as a date recognition pattern because this comes next to the colloquial way to talk about dates in Germany. The current year is then inserted automatically.
In addition I would advise to NOT use month names when entering dates. However, an En(GB) locale should understand a typed in 3 May as the third of may in the current year.
Output:
Input:
A global Canadian locale resets the date recognition pattern to
M-D;M/D/Y;M/D;Y-M-D
Y-M-D is obsolete because 1999-12-31 is recognized anyway, regardless of the locale setting.
I would change the recognition pattern to D+;D+M;D+M+Y
All these patterns are accessible from the numpad of a PC keyboard.
Thank you for the replies. I know changing to English (Canada) is a workaround, how to edit the locale settings? Probably this current behavior needs a PR filing. It’s an “Enhancement”.
I don’t want it to default display as “03/05/20” either. Creating a template doesn’t work, as it treats all cells as Date oddly
Just try what I told you. The input pattern has nothing to do with the output format.
Your example shows entering 1+ and a date being produced. Therefore it is not a valid solution to the question.
My desire is to enter a variety of numbers, and have no dated produced.
eg
1
1.5
6.1111
10
Where I do enter a date, eg “3 May” I don’t want to see “03/05/2026” in the box. I would like to see “2026-05-03”
I appreciate the video, however it doesn’t follow what I’ve asked. I’ve explained several times.
Maybe look back over my messages. Be sure any answer directly relates to the steps I specified please. I don’t want to use up your time further.
Canada will not recognise 3 May 2026; it will recognise May 3 2026.
.
Without changing locale, the simplest way is to create a cell style with date format YYYY-MM-DD, name it Date ISO or similar and make a default template with the cell style in it.
Create a default template from the sheet
Open a new spreadsheet and apply the cell style to a column, row or cell. It doesn’t automatically make all cells change a date to ISO but makes it easier to apply the format by simply selecting the range and double clicking Date ISO in the styles pane of the sidebar. You can add a style as an icon in the toolbar using the Customise dialogue.
.
Otherwise
Ok, it seems you want to create your own locale as in
There is no easy way to do this. But as this is open-source software you may change everything.
I read they have even introduced a name for a locale-setting of klingon language, so you can create or occupy something unused. Be aware, that ab"locale" only known on your computer may impact portability of your documents. So I will not recommend this.
It just follows what you were doing obviously without following what I suggested to you.
Quite obviously, you applied ISO format to cell style “Default”, which was not what I suggested.
Which obviously does happen with the “English(Canadian)” locale.
I applied to “Date” style, as I mentioned. Maybe you can try my suggestion, and make a video following the steps. I’m not interested in running a Canadian locale, but thank you for the suggestion.
@richy3009, since there seems to be some kind of miscommunication, I’d like to suggest to cut the procedure in smaller steps, before reaching the final result that you actually want.
Let’s start by the most basic steps.
Please select the relevant cells (or column, or whichever area) that are expected to show you Dates in the ISO 8601 format.
Press [CTRL]+[1] in order to open the Format Cells dialog.
In the Number tab, there is a “Format Code” field in the lower area of the dialog window. In that field, please type-in the following code: YYYY-MM-DD and press OK.
All this is unrelated to the Locale (at this stage), and it is independent from the way you initially type-in the original Date. This is only influencing the way the Dates are displayed.
In the cells that you previously selected, do you see the Dates in the format that you wanted?
