[excel] How do I convert a calendar week into a date in Excel?

I have a week number and a year, and would like to calculate the date of the Monday for that specific week in Microsoft Excel.

Year   Week   Date (Monday)
2012   1      January 2, 2012
2013   16     April 15, 2013
2014   42     October 13, 2014

What formula can I use to convert a calendar week to a specific date?

=(MOD(R[-1]C-1,100)*7+DATE(INT(R[-1]C/100+2000),1,1)-2)

yyww as the given week exp:week 51 year 2014 will be 1451

If the week number is in A1 and the year in A2, you can try:

A1*7+DATE(A2,1,1)

If your week number is in A1 and the year is in A2, following snippet could give you dates of full week

=$A$1*7+DATE($B$1,1,-4) through =$A$1*7+DATE($B$1,1,2)

Of course complete the series from -4 to 2 and you'll have dates starting Sunday through Saturday.

Hope this helps.

The following formula is suitable for every year. You don't need to adjust it anymore. The precondition is that Monday is your first day of the week.

If A2 = Year and Week = B2
=IF(ISOWEEKNUM(DATE($A$2;1;1)-WEEKDAY(DATE($A$2;1;1);2)+1)>1;DATE($A$2;1;1)-WEEKDAY(DATE($A$2;1;1);2)+1+B2*7;DATE($A$2;1;1)-WEEKDAY(DATE($A$2;1;1);2)-6+B2*7)

If you are looking for the opposite to get a date from a weeknumber i found a solution online and changed it slightly:

Function fnDateFromWeek(iYear As Integer, iWeek As Integer, iWeekDday As Integer)
  ' get the date from a certain day in a certain week in a certain year
  fnDateFromWeek = DateSerial(iYear, 1, (iWeek * 7) _
          + iWeekDday - Weekday(DateSerial(iYear, 1, 1)) + 1)
End Function

I took the formular from asap-utilities.com/ and changed

DateSerial(iYear, 1, ((iWeek - 1) * 7) 

to

DateSerial(iYear, 1, (iWeek * 7)

Update

It seems that at least for germany the formular works not as expected for the leap year 2016 so i changed it to this

Function fnDateFromWeek(iYear As Integer, iWeek As Integer, iWeekDday As Integer)
' get the date from a certain day in a certain week in a certain year
  
    If isLeapYear(iYear) Then
        curDate = DateSerial(iYear, 1, ((iWeek) * 7) _
          + iWeekDday - Weekday(DateSerial(iYear, 1, 1)) + 1)
    Else
        curDate = DateSerial(iYear, 1, ((iWeek - 1) * 7) _
          + iWeekDday - Weekday(DateSerial(iYear, 1, 1)) + 1)
    End If
    fnDateFromWeek = curDate
End Function

Since 2016 hardcoded is not ideal you could check if a year is a leap year with this function

Function isLeapYear(iYear As Integer) As Boolean

    If (Month(DateSerial(iYear, 2, 29)) = 2) Then
        isLeapYear = True
    Else
        isLeapYear = False
    End If
    
End Function

For a non-leap-year DateSerial(iYear,2 ,29) returns 1st of march

This may still be wrong but my limited test gave the expected results:

Sub TestExample()
   Debug.Print Format(fnDateFromWeek(2014, 48, 2), "ddd dd mmm yyyy") ' mo 24 Nov 2014
   Debug.Print Format(fnDateFromWeek(2015, 11, 6), "ddd dd-mmm-yyyy") ' fr 13 Mar 2015
   Debug.Print Format(fnDateFromWeek(2016, 36, 2), "ddd dd-mmm-yyyy") ' Mo 05 Sep 2015
End Sub

If A1 has the week number and year as a 3 or 4 digit integer in the format wwYY then the formula would be:

=INT(A1/100)*7+DATE(MOD([A1,100),1,1)-WEEKDAY(DATE(MOD(A1,100),1,1))-5

the subtraction of the weekday ensures you return a consistent start day of the week. Use the final subtraction to adjust the start day.

A simple solution is to do this formula:

A1*7+DATE(A2,1,1)

If it returns a Wednesday, simply change the formula to:

(A1*7+DATE(A2,1,1))-2

This will only work for dates within one calendar year.