CODEWITHSUNDEEP
google-sheetsgoogle-sheets-query
FlorianT.
FlorianT.

Reputation: 873

How to get month number by week number? Google Sheets

I have a column with weeknumbers incrementing from 1 to 42. Next to it I would like to have the corresponding monthnumbers from 1 to 12. So e.g. next to week 1, 2, 3 it would be month 1.

How would I achieve this in google sheets?

This is what it looks like

Best Florian

Upvotes: 1

Views: 10995

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18707

It's possible if you'll count concrete dates.

A1:

=ARRAYFORMULA( (ROW(INDIRECT("a1:a"&42)) - 1) * 7 + today())

  • adjust the date, change today() to your start date.

B1:

=FILTER(WEEKNUM(A1:A,1),A1:A<>"")

  • adjust week type if needed, change 1

C1:

=FILTER(MONTH(A1:A),A1:A<>"")

enter image description here

Upvotes: 2

pnuts
pnuts

Reputation: 59475

A week number may span a month end, so two different months for different days in the same week. ie

Not possible.

Upvotes: 3

Related Questions