Google-sheets – How to turn a list of months string into a list of month numbers

google sheets

I have a table with month headers at the top:

January | February | March | ...

Is there a way with one formula that I can get a list of month numbers?

January | February | March | ...
--------+----------+-------+----
      1 |        2 |     3 | ...

I saw this answer about turning a month string into a month number with =MONTH(DATEVALUE(A1 & " 1")) but when I tried to do =ARRAYVALUE(MONTH(DATEVALUE(A1:1 & " 1"))) it didn't work. In fact MONTH(DATEVALUE(A1:1 & " 1")) gives the same result as MONTH(DATEVALUE(A1 & " 1")).

Best Answer

There is no arrayvalue command; there is arrayformula.

Yes, MONTH(DATEVALUE(A1:1 & " 1")) gives the same result as MONTH(DATEVALUE(A1 & " 1")) because without arrayformula, it expects one value as an argument. Given more, it only takes the first one.

The correct formula (tested in my spreadsheet) is

 =ARRAYFORMULA(MONTH(DATEVALUE(A1:1 & " 1")))
Related Topic