0

Going to a new cell, if I type 07/22, if the column data format is set to a date, it interprets this to July 22, 2020 (where 2020 is the current year). I want it to use the 22 as the year, I don't mind if behind the scenes it translates to July 1, 2022 or July 31, 2022 (although I prefer the 1st).

I have the data column set to Month (08)/Year (30) i.e. expected output is exactly as typed 07/22. But even with this on, Sheets interprets the entry as Month, Day.

4
  • What is the locale of your spreadsheet?
    – Tedinoz
    Oct 29 '20 at 1:56
  • United States / (GMT-05:00) Oct 29 '20 at 2:19
  • @marikamitsos has covered the non-negotiable aspects of date entry in Google Sheets. You have not explained the layout of your spreadsheet, but a possible option might be to use a formula along the lines of =date(2022,month(C6),1) (where cell C6 contains the 7/22 "date" that you entered). If this formula is formatted as Month/Year, it will display "7/22".
    – Tedinoz
    Oct 29 '20 at 7:22
  • Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Oct 29 '20 at 17:39
2

...if I type 07/22, if the column data format is set to a date, it interprets this to July 22, 2020 (where 2020 is the current year).

The behaviour cannot be changed.

I don't mind if behind the scenes it translates to July 1, 2022 or July 31, 2022

That is what you need to write as well.

I have the data column set to Month (08)/Year (30) i.e. expected output is exactly as typed 07/22. But even with this on, Sheets interprets the entry as Month, Day.

This is just the formatting.
You still have to write a date as day/month/year or month/day/year pending on your locale.

("When typing a date into cell as 12/24". Just think that this could be interpreted as hours, as a fraction etc...)

4
  • Just to be clear, a user should enter 01/07/22 and format the cell as Month/Year.
    – Tedinoz
    Oct 29 '20 at 7:15
  • 1
    @Tedinoz To be clearer "a date as day/month/year or month/day/year pending on your locale". I am saying that because one could also write 01-07-22 and Google will still understand a date. Oct 29 '20 at 17:35
  • “because one could also write 01-07-22 and Google will still understand a date. ” Indeed, an important distinction. Thank you.
    – Tedinoz
    Oct 30 '20 at 9:13
  • Well I guess an answer I don't want to hear is still a correct answer. Thank you. Oct 31 '20 at 23:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.