Date format in sheets is not updating correctly

The YYYYMM should be 2022/03, but it shows 2543/08…?

I’m working on a case study and the dates are set in google sheets as YYYYMM. When I go to reformat the dates into YYYY/MM, it gives the year 2453…? It should be 2020.
I have to use these in pivot tables, so I need them to be corrected. I’m in the correct locale and all settings are fine. How can I fix this?

I tried to do custom formatting, as well as normal formatting, still to no avail. I don’t believe Google Sheets understands YYYYMM as a date format

>Solution :

Those values are not "set as YYYYMM" as you mentioned. They are just a text string reading "202303" or a number value reading 202,303

Spreadsheets keep track of dates as "the number of days since December 30th, 1899"

so 202,303 days since then happens to be in August of the year 2543.

You instead need to make a formula in a different cell to construct the month and year from the text itself. Something like: =DATE(1*LEFT(A2,4),1*RIGHT(A2,2),1)
Then choose the cell and Format>Number>Custom and enter: YYYY/MM

Leave a Reply