NOW LIVE: The Forum 'Ask An Expert' event. The theme is ENERGY. Please post your questions on bills, switches, alternative fuels etc. Our expert MSE Andrew will answer as many as possible
Google Sheets number format not playing nice

B0bbyEwing
Forumite Posts: 865
Forumite

in Techie Stuff
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).
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).
0
Comments
-
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.0
-
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.
For some things I use Excel, other things where it's handier I'll use Sheets.0 -
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.
Stompa1 -
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.
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.0 -
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.
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.Stompa0
Categories
- All Categories
- 338.8K Banking & Borrowing
- 248.6K Reduce Debt & Boost Income
- 447.5K Spending & Discounts
- 230.7K Work, Benefits & Business
- 600.8K Mortgages, Homes & Bills
- 171K Life & Family
- 244K Travel & Transport
- 1.5M Hobbies & Leisure
- 15.9K Discuss & Feedback
- 15.1K Coronavirus Support Boards