Google sheets recently changed American datetime format, how do I change it back?

Question

Google sheets recently changed American datetime format, how do I change it back?

It used to default to using "MM/dd/yyyy" but as of a couple weeks ago now when I enter a date it defaults to "MM/dd" and I have to go manually reformat it.

And worst part is a column never "remembers" it's formating. Every time I open the sheet and start editing a date/time column I have to go back and remind Google Sheets that I want that column to be a date/time column formated as "MM/dd/yyyy".

Is there anyway to change my own date/time default back to "MM/dd/yyyy"? (Yes all my sheets are set to USA country.)

Is there anyway to make Google Sheets remember a column's formatting every time it closes and gets reopened?

Answer 1

In my experience, the cells remember any format they were given. Once a column is formatted M/d/yyyy it should stay that way.

There is no setting (other than the country setting) that controls the default date format. But entering the following script in the Script Editor (under Tools) will automatically format any dates you enter or edit from now on as M/d/yyyy.

function onEdit(e) {
  if (e.range.getNumberFormat() == 'm/d') {
    e.range.setNumberFormat('M/d/yyyy');
  }
}

---

Technical note: M/d/yyyy means 6/27/2016, while MM/dd/yyyy is 06/27/2016. If you prefer leading zeroes, change the setNumberFormat accordingly.

---

Another technical note: Google using m/d instead of M/d is inconsistent with SimpleDateFormat specification that they otherwise follow. This is something they decided to do a while back, to allow m instead of M in date formats.