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

 

[shg]

OK, one more chance:

Function YW2Date(yyyyww As Long, _
                 Optional iDay As Long = 1) As Variant
    ' Converts the year yyyy and ISO week number ww to an iDay date
    ' (1 to 7 = Monday to Sunday)

    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 - Weekday(Jan1, vbMonday) _
                  + IIf(Weekday(Jan1, vbMonday) > 4, 8, 1)        
        YW2Date = YW2Date + iDay - 1 + 7 * (iWk - 1)
    End If
End Function

 

[DutchKevin]

Hello Barry, SHG,
Thanks so much for looking into this one.
Your last post works flawlessly. I tested at a few year-end's and it gives the correct result. Even the occasional week 53 (1 day long in 2012..) was no problem.
It will take some time to get my head around how it works, but I will be using this formula a lot in the future.

Thanks again for helping me out.

Best Regards
Kevin

 

[shg]

Glad it worked for you, Kevin, good luck.