How do I convert a calendar week into a date in Excel

Question

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

User · Answer

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.

User · Answer

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

User · Answer

For ISO week numbers you can use this formula to get the Monday   DATE A2 1 -2 -WEEKDAY DATE A2 1 3   B2 7  assuming year in A2 and week number in B2  it s the same as my answer here https   stackoverflow com a 10855872 1124287

User · Answer

If the week number is in A1 and the year in A2  you can try   A1 7 DATE A2 1 1

User · Answer

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    quot ddd dd mmm yyyy quot     mo 24 Nov 2014    Debug Print Format fnDateFromWeek 2015  11  6    quot ddd dd-mmm-yyyy quot     fr 13 Mar 2015    Debug Print Format fnDateFromWeek 2016  36  2    quot ddd dd-mmm-yyyy quot     Mo 05 Sep 2015 End Sub

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to date

Examples related to excel-formula