12

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.

Paul B.
  • 2,394
  • 27
  • 47
Pricey
  • 5,799
  • 12
  • 60
  • 84

3 Answers3

27

You can actually do this a little more simply. If Year is in A2, e.g. 2012, and week number is in B2 you can get the start date of that week with this formula

=DATE(A2,1,-2)-WEEKDAY(DATE(A2,1,3))+B2*7

format as date

......clearly you can wrap that in a MONTH function to get the month, i.e.

=MONTH(DATE(A2,1,-2)-WEEKDAY(DATE(A2,1,3))+B2*7)

format result cell as general

Explanation

The above finds the date of the last Monday of the previous year and then adds the number of weeks * 7 to give the start date of the relevant week.

The first day of the year for ISO weeks is always the only Monday in the period 29th Dec to 4th Jan, so to find that date we can find the Monday immediately before 5th Jan.

To find the Monday immediately before any date you can use this generic formula

=date-WEEKDAY(date-2)

so if date is 5th Jan of the year shown in A2 that's the same as

=DATE(A2,1,5)-WEEKDAY(DATE(A2,1,3))

.....but that gives the first Monday of this year, so if I want the last Monday of the previous year I can subtract 7 - one way to do that is to subtract 7 from the 5 to get -2 hence:

=DATE(A2,1,-2)-WEEKDAY(DATE(A2,1,3))

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Thanks, this stuff confuses me with what is actually going on, -2 from the first month of the year then minus the weekday num for the 3rd day of January for that year... then add the weeks * days in a week. Your answer looks good and much cleaner +1. Thanks – Pricey Jun 06 '12 at 08:53
  • 1
    Takes some getting your head round the logic but a clean and simple formula great answer – MikeT Jun 17 '14 at 10:25
4

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))

Paul B.
  • 2,394
  • 27
  • 47
  • Great answer thanks, took a bit of working out that D4 was the week 1 of the year when working out the month calculation. Does the job for me, I did notice that it has a problem with leap years like 2012, where week 18 starts April 30th but the above formulae returns week 5 when it should be week 4. Fortunely I don't have to be too accurate with this, so thanks again. – Pricey Jun 01 '12 at 15:42
  • Interesting, it seems to work for me. For year 2012, week 18 C4 evaluates to 52 (Jan 1st is week 52 of 2011). D4 is Monday, Jan 2nd 2012 and (D4+7*(18-1)) should be April 30th. Maybe I made a mistake since a had to translate the formulas. I'll update the post if you let me know where the error lies. – Paul B. Jun 01 '12 at 16:17
  • Ah I know what my problem is. There is a reference to cell B1 in the formula that works out week 1. I don't have any value for that particular cell, I assume its B4 so I adjusted it and it works now. – Pricey Jun 01 '12 at 16:40
  • Thanks, fixed it! Turned out there was another error in the formula (+WEEKDAY instead of -WEEKDAY). – Paul B. Jun 01 '12 at 17:37
1

The ISO Week always contain January 4th, so let's get its weekday starting with monday (obviously "year" is the cell with the year):

WEEKDAY(DATE(year;1;4))

This is the number of days we need to go back from that date to find a monday (plus one)

DATE(year;1;4)-WEEKDAY(DATE(year;1;4))+1

So now we just need to count on...

DATE(year;1;4)-WEEKDAY(DATE(year;1;4))+1+weeknumber*7
Envite
  • 321
  • 2
  • 6
  • 16