Google-sheets – Trying to convert this date format “’year|month” into name of Month using Google Sheets

google sheets

I'm facing an issue when trying to convert a specific date format into name of month. Supermetrics, an addon for Google Spreadsheets, returns date formats like the following: "2016|08", etc.

I have tried several solutions from the documentation without any luck. Any solutions or specific functions that I should look for?

Best Answer

In order to avoid unnecessary calculations, it's assumed that the year doesn't matter, if it does, replace 1900 by LEFT(A1,4).

Alternative 1

=Date(1900,Right(A1,2),1) and apply MMMM as custom format

If A1 value is 2016|08 the result value will 1/08/1900 (assuming that the locale short date format is dd/mm/yyyy) but August will be displayed.

Alternative 2

=TEXT(Date(1900,Right(A1,2),1),"MMMM")

If A1 value is 2016|08 the result value will be August.

Related Solutions

Google Sheets Date Format – How to Format Dates in Spreadsheets

Formatting is not the answer, because it doesn't affect the underlying values. You need to make the values updatable instead.

One way is to make a reference-data place somewhere in one of your sheets (or maybe add a new sheet just for that), and put the year value in there.

Then in the first cell where you want the date values to appear, use this formula:

=date(B1,01,01) except replace B1 with the value of the cell where you put the year value.

In the cell immediately above or beside it, say =D1+1 except replace D1 with the reference to the cell where you put the first forumula.

Then to make a new file for next year, just save-as and change the one reference value.

Google Sheets – How to Convert Month-Year Text to Date Format

You can use

=datevalue("01-"&B2)

(single-cell formula that can be copied down), or

=arrayformula(datevalue("01-"&B2:B))

(converts an entire column at once), or

=arrayformula(iferror(datevalue("01-"&B2:B)))

(does the same, while suppressing any error messages).

This works because the strings like 01-Oct-14 are understood by datevalue command correctly, at least in your locale.