Google Sheets number format not playing nice

B0bbyEwing

This has happened to me on & off for a long time now & it's now annoyed me enough to ask what is going on with it. Some times it works, most times it doesn't.

So Google Sheets....

I highlight a cell, call it A1 & I'll be focusing on the A column. I may even hit A to highlight the entire column.

Format > Number > Customer Number Format
In here I'll select dd/mm/yyyy.

I go back to the cell & I'll type in 25-07-23 just like that. It does absolutely nothing. It stays like that. Doesn't format it to 2023 at all. 
HOWEVER, I have in the past been working on sheets where it DID format it after typing something like that - no consistency (aside from consistently not doing it with the odd exception).

But in the sheet that does it, it will randomly not do it. I'll go to the next cell, type in the date & it'll stay like that, so I have to highlight the cell above, drag down, edit the cell I wanted to type in & finally it does it as it should again.

Top & bottom is, I want it to enter a date as I'm selecting & It's not doing it. Why not & how do I get it to do what I want?

Oh & for the record, since the menu system uses / and not -, I've also tried / as a separator & that didn't work either (this aside, on the sheet/s where it's worked in the past, I type date format as - out of habit & it worked in those anyway).

Le_Kirk

I don't use Google sheets but in Excel (other spreadsheets are available) you tell it the DATE format rather then NUMBER and then you can customise it like you showed.

B0bbyEwing

Le_Kirk said:

Don't use Google sheets but in Excel )other spreadsheets are available) you tell it the DATE format rather then NUMBER and then you can customise it like you showed.

Yeah I don't have an issue in Excel.

For some things I use Excel, other things where it's handier I'll use Sheets. 

Stompa

25-07-23 remains as 25-07-23, but if you try 08-07-23 it will be shown as 07/08/2023 (i.e. with mm & dd swapped).

I suspect the problem is that your locale is set to 'United States', so it'll be expecting dates to be entered in US mm/dd/yy format. Try changing it to UK (File > Settings > Locale). If an invalid date is entered then I believe it gets treated as text.

With the locale set to US then obviously some dates you enter (where mm<=12) will appear to be valid ones, others (where mm>12) will be invalid, which would explain why it appears to work sometimes. Though in reality it will always be swapping mm & dd.

B0bbyEwing

Stompa said:

25-07-23 remains as 25-07-23, but if you try 08-07-23 it will be shown as 07/08/2023 (i.e. with mm & dd swapped).

I suspect the problem is that your locale is set to 'United States', so it'll be expecting dates to be entered in US mm/dd/yy format. Try changing it to UK (File > Settings > Locale). If an invalid date is entered then I believe it gets treated as text.

With the locale set to US then obviously some dates you enter (where mm<=12) will appear to be valid ones, others (where mm>12) will be invalid, which would explain why it appears to work sometimes. Though in reality it will always be swapping mm & dd.

Hmm you may be on to something there. I'll have to check that out.

I've actually made a thread on that a while ago (the whole UK/US thing). It's frustrating as a frustrating thing - I follow the guides and do what they say but for whatever reason I cannot set it so that all my created documents are UK. They all start out as American, no matter what I do in settings. 

EDIT: Just to confirm, you've solved it. Thank you very much.

Though it is still frustrating that I have to make every document UK rather than US. I can't just make my account UK & everything created after that point is UK ... doesn't happen.

Stompa

B0bbyEwing said:

Though it is still frustrating that I have to make every document UK rather than US. I can't just make my account UK & everything created after that point is UK ... doesn't happen.

The method described in the last post here:

https://webapps.stackexchange.com/questions/142006/how-does-one-set-the-default-locale-for-each-new-spreedsheet-in-google-sheets

appears to work for me, though using United Kingdom rather than Australia of course.