calculate the month from the year and week number in excel
date, excel, week-number
Solution
I suggest the following approach
Determine the (ISO) week of January 1st (see this page) [in cell C4]
`=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7)` (assuming B4 `=DATE([Year],1,1)`)
Calculate the day on which week 1 starts [in cell D4]
`=IF(C4=1,B4-WEEKDAY(B4,3),B4+7-WEEKDAY(B4,3))`
Determine the Monday of the week in question and the corresponding month
`=MONTH(D4+7*([Week]-1))`
Problem
In Excel 2007 I have a Year number and Week number and I want to work out the Month number. The catch is that in my case the start of every week is a monday, so some weeks will overlap through the years. Examples: ``` Year: 2012 Week 1 started: Monday 2nd January Sunday 1st January was in week 52 of 2011 ``` So given the below: ``` Year: 2011 Week: 10 ``` How can I work out that week 10 started on 7th March and therefore week 10 was in Month number 3. Thanks for any help on this.