date from weeknumber by UDF

[DutchKevin]

Hello All,

I'm using a UDF to get from a yyyyww notation to a real date. I take the yyyy value from a cell, use it in ISOYEARSTART and then add 7 times the number of weeks from the same cell

=IFERROR(IF(A3=0;"";ISOYEARSTART(LEFT(A3;4))+7*RIGHT(A3;2))+5;"")

I add 5 days, to end up on a friday, which is may target day of that week.

Although all this works fine, what would be the way to adapt the UDF to avoid the second part of the formula?
I'd like to get it into one UDF for easy references.

Any Advice?
Any other setup is also welcome, avoiding the yearstart maybe and going straight for the date?
Note: I must use ISO weeknumbers because of european standards. And I use european notation, hence the ; in stead of the , in the formula's.

This is the ISOYEARSTART code

Public Function ISOYEARSTART(WhichYear As Integer) As Date
' First published by John Green, Excel MVP, Sydney, Australia
    Dim WeekDay As Integer
    Dim NewYear As Date
    NewYear = DateSerial(WhichYear, 1, 1)
    WeekDay = (NewYear - 2) Mod 7
    If WeekDay < 4 Then
        ISOYEARSTART = NewYear - WeekDay
    Else
        ISOYEARSTART = NewYear - WeekDay + 7
    End If
End Function

 

[taurean]

Try like this:
=WeekEndDate(LEFT(A3;4);RIGHT(A3;2))

Public Function WeekEndDate(WhichYear As Integer, WhichWeek As Integer) As Date
' First published by John Green, Excel MVP, Sydney, Australia
Dim WeekDay As Integer
Dim NewYear As Date
NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7
If WeekDay < 4 Then
    WeekEndDate = NewYear - WeekDay + (7 * WhichWeek + 5)
Else
    WeekEndDate = NewYear - WeekDay + (7 * (WhichWeek + 1) + 5)
End If
End Function

 

[DutchKevin]

Hello Shrivallabha
Sorry for not posting back sooner. I've in the meanwhile been occupied with other things.
Your suggestion works very well, (thanks for that !!!) I'd like to ask if it can be condensed even more?
I'm a bit puzzled about how to build a proper public funtion.
Can it be written into the function so that "WhichYear" and "WhichWeek" are taken automatically from the targetcell?
In that way the user would only have to use: WeekendDate(A3).

Regards
Kevin

 

[shg]

Maybe ...

Public Function YW2Fri(yyyyww As Long) As Variant
    Dim iYr         As Long
    Dim iWk         As Long
    Dim Jan1        As Date

    iYr = yyyyww \ 100
    iWk = yyyyww Mod 100
    
    If iYr = 0 Or iWk > 53 Then
        YW2Fri = vbNullString
    Else
        Jan1 = DateSerial(iYr, 1, 1)
        YW2Fri = Jan1 + (9 - Weekday(Jan1)) Mod 7 + 4 + 7 * iWk
    End If
End Function

E.g.,

       --A--- --------B-------- -------C-------
   1   201001 Friday 01/15/2010 B1: =YW2Fri(A1)
   2   201101 Friday 01/14/2011                
   3   201201 Friday 01/13/2012                
   4   201301 Friday 01/18/2013                
   5   201401 Friday 01/17/2014                
   6   201501 Friday 01/16/2015                
   7   201601 Friday 01/15/2016                
   8   201701 Friday 01/13/2017                
   9   201801 Friday 01/12/2018                
  10   201901 Friday 01/18/2019                
  11   202001 Friday 01/17/2020                
  12   202101 Friday 01/15/2021

 

[shg]

This is a little more flexible:

Function YW2Date(yyyyww As Long, _
                 Optional iDay As VbDayOfWeek = vbMonday) As Variant
    ' Converts the year yyyy and ISO week number ww to an iDay date
    ' (1 to 7 = Sunday to Saturday)
    
    Dim iYr         As Long
    Dim iWk         As Long
    Dim Jan1        As Date

    iYr = yyyyww \ 100
    iWk = yyyyww Mod 100

    If iYr = 0 Or iWk > 53 Or _
       iDay < vbSunday Or iDay > vbSaturday Then
        YW2Date = vbNullString

    Else
        Jan1 = DateSerial(iYr, 1, 1)
        YW2Date = Jan1 _
                  + (9 - Weekday(Jan1)) Mod 7 _
                  + iDay - vbMonday _
                  + 7 * (iWk - 1)
    End If
End Function

      --A--- --------B-------- ---------C----------
  1   201001 Friday 01/08/2010 B1: =YW2Date($A1, 6)
  2   201101 Friday 01/07/2011                     
  3   201201 Friday 01/06/2012                     
  4   201301 Friday 01/11/2013                     
  5   201401 Friday 01/10/2014                     
  6   201501 Friday 01/09/2015                     
  7   201601 Friday 01/08/2016

 

[barry houdini]

Shouldn't week 1 start on the first Monday after 28th December, so Friday of week 1 in 2012 would be Friday 6th January? If so then in your original formula you would subtract 3 rather than adding 5, i.e.

=IFERROR(IF(A3=0;"";ISOYEARSTART(LEFT(A3;4))+7*RIGHT(A3;2))-3;"")

[that would tally with the results from shg's last UDF]

 

[shg]

Indeed, thanks, DLL.

 

[barry houdini]

Actually, no, my mistake, some of the results tally but not all. Week 1 in ISO weeks always starts on the Monday in the range 29th Dec to 4th Jan so consequently Friday of week 1 will always be somewhere between 2nd and 8th January so that doesn't match your results for 2013, 2014 and 2015

 

[shg]

I had thought, from Chip Pearson's description, that it's the first Monday of the year -- my misreading. Correction forthcoming.

I also see that Monday is day 1.

 

[barry houdini]

The ISOYEARSTART UDF quoted here gives the correct start date of week 1 as far as I can tell, i.e. first Monday on or after December 29 of the previous year