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.

Improve this question
4
  • What is the locale of your spreadsheet?
    – Tedinoz
    Commented Oct 29, 2020 at 1:56
  • United States / (GMT-05:00)
    – CeePlusPlus
    Commented Oct 29, 2020 at 2:19
  • 1
    @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
    Commented Oct 29, 2020 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.
    – marikamitsos
    Commented Oct 29, 2020 at 17:39

1 Answer 1

Reset to default
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...)

Improve this answer
4
  • Just to be clear, a user should enter 01/07/22 and format the cell as Month/Year.
    – Tedinoz
    Commented Oct 29, 2020 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.
    – marikamitsos
    Commented Oct 29, 2020 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
    Commented Oct 30, 2020 at 9:13
  • Well I guess an answer I don't want to hear is still a correct answer. Thank you.
    – CeePlusPlus
    Commented Oct 31, 2020 at 23:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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