Google Sheets, convert "2020-January" to "2020-01" and so on

Question

I have a column with data like "2020-January", "2020-February" and so on. I want to transform the data into "2020-01" , "2020-02" , how could I go about it?

I have tried month(&1), but that seems to only transform "January", but if the year value like 2020 is present, the formula fails.

If this would be Excel, my first question would be if the mentioned dates are recognised as dates (which you can verify by subtracting 1 and see if you get a correct result), or are those dates just seen as plain strings (in that case you need to do some basic string handling routines). — Dominique

Harun24HR Harun24HR · Accepted Answer · 2020-09-01T09:09:59

If your data are stored as date then use below formula.

=TEXT(A1,"yyyy-mm")

If data are stored as text then use below formula.

=TEXT("1-" & MID(A1,6,3) & "-" & LEFT(A1,4),"yyyy-mm")

Google Sheets, convert "2020-January" to "2020-01" and so on

1 Answers