We'd like to remind Forumites to please avoid political debate on the Forum. This is to keep it a safe and useful space for MoneySaving discussions. Threads that are - or become - political in nature may be removed in line with the Forum’s rules. Thank you for your understanding.

Google Sheets number format not playing nice

Options
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).




Comments

  • Le_Kirk
    Le_Kirk Posts: 22,629 Forumite
    First Anniversary First Post Photogenic Name Dropper
    edited 26 July 2023 at 4:09PM
    Options
    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
    B0bbyEwing Posts: 1,256 Forumite
    First Post First Anniversary Name Dropper
    Options
    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
    Stompa Posts: 8,348 Forumite
    Name Dropper First Post First Anniversary
    Options
    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.



    Stompa
  • B0bbyEwing
    B0bbyEwing Posts: 1,256 Forumite
    First Post First Anniversary Name Dropper
    edited 29 July 2023 at 5:34PM
    Options
    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
    Stompa Posts: 8,348 Forumite
    Name Dropper First Post First Anniversary
    Options
    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.
    Stompa
Meet your Ambassadors

Categories

  • All Categories
  • 344.6K Banking & Borrowing
  • 250.6K Reduce Debt & Boost Income
  • 450.4K Spending & Discounts
  • 236.8K Work, Benefits & Business
  • 610.6K Mortgages, Homes & Bills
  • 173.8K Life & Family
  • 249.5K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.9K Discuss & Feedback
  • 15.1K Coronavirus Support Boards