CODEWITHSUNDEEP
vbaexcel
Jenny
Jenny

Reputation: 451

Convert a weeknumber to month and generate an chart

I have an sheet with the Table, having Column A in Calendar Week. I would like to generate an chart with x axis as month with the data available in the table.

Could anyone help me to code it ? I am struck how I can convert an weeknumber in the column to month.

In the sample image column A is the week number denoted from 1 to 53 for the year 2017 and have few data in the corresponding columns. If I am generating the chart for the data, I will have the week number in my X-axis. Instead I would like to have them with respected to month(Jan, Feb till Dec) .

Could anyone suggest, how I can generate a chart for these data with month in my x/axis. I am not getting an clue to do this. Any lead would be helpful.

Upvotes: 0

Views: 571

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57743

This is a formula to convert the week number in cell A1 into month

=MONTH(DATE(2017,1,-2)-WEEKDAY(DATE(2017,1,3))+A1*7)

Note that you also need to know the year the week number refers to, because it can differ from year to year. In this example it is fixed to 2017.

Note that week number != week number.
There are different methods how week numbers are calculated.
This formula example is based on ISO week numbers, with a week starting on Monday and the week containing the 1st Thursday of the year is considered week 1. For example, in the year 2016, the first Thursday is January 7, and that is why week 1 begins on 4-Jan-2016.

Here is some info about week number calculation based on different week numbers: Week Numbers In Excel

Upvotes: 2

Rob Anthony
Rob Anthony

Reputation: 1813

You need to use the formula

=TEXT((((A1-1)*7)+DATE(2017,1,1)),"m")

This will turn the week number into a date, then extract the month number

Upvotes: 1

Related Questions