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?

score 59 · Accepted Answer · edited May 23 '17 at 12:17

59

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

edited May 23 '17 at 12:17

Community

1
1

answered Jun 12 '12 at 13:05

barry houdini

45,615
8
63
81

This works ok but you have to use semicolons instead of commas. – besimple Jul 08 '15 at 08:47
4

Whether you use commas or semi-colons as "argument separators" is dependant on your regional settings - for me it works with commas. In regions where a comma is usually used as the decimal separator then usually semi-colons will be required – barry houdini Jul 08 '15 at 08:53

score 2 · Answer 2 · edited Jan 07 '15 at 11:52

2

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)

edited Jan 07 '15 at 11:52

shankar.parshimoni

1,289
5
22
42

answered Jan 07 '15 at 10:31

Mario

21
1

score 1 · Answer 3 · answered Apr 05 '16 at 13:24

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.

score 0 · Answer 4 · answered Jun 12 '12 at 11:47

0

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

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

answered Jun 12 '12 at 11:47

tofcoder

2,352
1
20
28

that won't necessarily give you a Monday date though? – barry houdini Jun 12 '12 at 13:06

score 0 · Answer 5 · edited May 02 '13 at 02:59

0

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.

edited May 02 '13 at 02:59

slm

15,396
12
109
124

answered May 02 '13 at 02:39

rogertwose

1

score 0 · Answer 6 · edited Apr 06 '16 at 03:40

0

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.

edited Apr 06 '16 at 03:40

sampathsris

21,564
12
71
98

answered May 24 '14 at 19:55

Richard Kernick

1

score 0 · Answer 7 · answered Oct 30 '14 at 04:40

0

=(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

answered Oct 30 '14 at 04:40

waqar piracha

1

surfmuggle · Answer 8 · 2020-11-21T01:23:34.123

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

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

8 Answers8

Update

Linked