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
B0bbyEwing Forumite Posts: 865
500 Posts First Anniversary Name Dropper
Forumite
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 Forumite Posts: 20,974
    Ninth Anniversary 10,000 Posts Photogenic Name Dropper
    Forumite
    edited 26 July at 4:09PM
    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 Forumite Posts: 865
    500 Posts First Anniversary Name Dropper
    Forumite
    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 Forumite Posts: 8,334
    Part of the Furniture 1,000 Posts Name Dropper
    Forumite
    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 Forumite Posts: 865
    500 Posts First Anniversary Name Dropper
    Forumite
    edited 29 July at 5:34PM
    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 Forumite Posts: 8,334
    Part of the Furniture 1,000 Posts Name Dropper
    Forumite
    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
  • 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