Date formatting in Google Sheets is not working according to the instructions

user_15093439 0
Viewed 13421

Yes, I know; this question has been asked thousands of times in hundreds of different places, but Google Sheets stubbornly refuses to let me convert a date such as "9/18/2004" to "2004-9-18". I select one or more cells whose date format is "dd/mm/yyyy", click on FORMAT, then NUMBER, then MORE FORMATS, then I select "More date and time formats", and then select the example "1930-08-05", and then nothing happens. Nothing changes. I need help with this. Here is a link to the problem column: https://docs.google.com/spreadsheets/d/1gYdPcNO-Jq1SFUpdb88o7KKVqG5_ia9NpD_3T98CkEw/edit?usp=sharing

Thanks.

google-sheets formatting date
3 Answers

From Google:

Change locale and time zone When you change the locale and time zone of a spreadsheet, it changes the spreadsheet’s default currency, date, and number formatting.

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Click File and then Spreadsheet settings.
  3. Under "General," click the "Locale" and "Time zone" menus to change your settings.
  4. Click Save settings.

Changing the locale doesn’t change your language settings in Google Sheets. You can set the language in Google Account settings.

Worked for me :)

user_165965110

9/18/2004 is not a date. its text string. if you change it to 18/9/2004 then you have a valid date and you can apply your desired formatting

to quickly reformat it you may use:

=ARRAYFORMULA(TEXT(IF(ISNUMBER(A2:A*1), A2:A, DATE(
 REGEXEXTRACT(A2:A, ".{4}$"), 
 REGEXEXTRACT(A2:A, "^\d+"), 
 REGEXEXTRACT(A2:A, "/(\d+)/"))), "yyyy-mm-dd"))

enter image description here

user_56326290

So this just worked for me:

Change settings as advised - but THEN:

  1. Insert a new column
  2. Copy all dates from the original column into the new on with "paste special" => "values only".
  3. Then select all dates and format again as date. From there on I could sort according to date etc.
user_169649530
Related