vba convert week number (and year) to date?

Question

I have a week number in Cell C13 and a year in Cell C14.

I am using the below formula to convert this into the Thursday of that week number's date:

=DATE(C14,1,-6)-WEEKDAY(DATE(C14,1,3))+C13*7

How could I do the same thing using VBA?

Amblyopia

If C13=1 and C14=2017, (first week of 2017), your formula returns 29 Dec 2016. Is that what you want? – Durno

As this wasn't stable regarding which year you use it in, I've amended it with a properly working function! ;) – Nolen

Answer 1

For a specific year, you can do it like this :

DateAdd("ww", WeekNumber - 1, DateSerial(2017, 1, 5))

And to test it :

Debug.Print Format(DateAdd("ww", WeekNumber - 1, DateSerial(YearNumber, 1, 5)), "ddd d MMM yy")

---

If others are looking into this and don't want a Thursday or don't work on 2017 :

1. change the 5 to fit your need!

2. Or use the below function GetDayFromWeekNumber

Code to test it :

Sub test()
    Debug.Print Format(GetDayFromWeekNumber(2017, 1, 4), "ddd d MMM yyyy")
End Sub

---

And the generic function GetDayFromWeekNumber :

Public Function GetDayFromWeekNumber(InYear As Integer, _
                WeekNumber As Integer, _
                Optional DayInWeek1Monday7Sunday As Integer = 1) As Date
    Dim i As Integer: i = 1
    If DayInWeek1Monday7Sunday < 1 Or DayInWeek1Monday7Sunday > 7 Then
        MsgBox "Please input between 1 and 7 for the argument :" & vbCrLf & _
                "DayInWeek1Monday7Sunday!", vbOKOnly + vbCritical
        'Function will return 30/12/1899 if you don't use a good DayInWeek1Monday7Sunday
        Exit Function
    Else
    End If

    Do While Weekday(DateSerial(InYear, 1, i), vbMonday) <> DayInWeek1Monday7Sunday
        i = i + 1
    Loop

    GetDayFromWeekNumber = DateAdd("ww", WeekNumber - 1, DateSerial(InYear, 1, i))
End Function

Nolen

With 2017 and 1 your function returns 12 Jan 2017. Is that the desired result? I would have thought 5 Jan 2017 – Durno

@RonRosenfeld : Thx for pointing it out! I forgot to add the -1 to week number! – Nolen

@RonRosenfeld : Thx again, as this wasn't stable regarding which year you use it in, I've amended it with a properly working function. – Nolen

The issue is that the OP has not defined what he means by the First Week of the year, so anyone writing a weeknumber related formula has to guess as to what he really means. Excel VBA includes four possible options; native Excel includes two; but I suppose there are others. – Durno

adapted this in a loop to create a whole week, very useful. – Kuhl

For those wondering. This does not work with ISO week, as used in Europe. – Syncretize

Answer 2

It works quite ok, taking in mind that the first day of the week is Sunday:

Function fnDateFromWeek(ByVal iYear As Integer, ByVal iWeek As Integer, ByVal iWeekDday As Integer)

        fnDateFromWeek = DateSerial(iYear, 1, ((iWeek - 1) * 7) + iWeekDday - Weekday(DateSerial(iYear, 1, 1)) + 1)

End Function

You have to pass which day do you want as third parameter. Thursday is day number 5.

Lovelorn

Answer 3

As an additional option, for those using the ISO weeknumber system, where Week 1 of the year is the first week of the year containing four days (or the week that includes the first Thursday of the calendar year, and the first day of the week is Sunday.

• DOW is an optional argument representing the day of the week. It will default to Thursday and, since we are using the ISO weeknumber system, should always fall within the current year.

---

Option Explicit
Function WNtoDate(WN As Long, YR As Long, Optional DOW As Long = 5) As Date
    'DOW:  1=SUN, 2=MON, etc
    Dim DY1 As Date
    Dim Wk1DT1 As Date
    Dim I As Long

DY1 = DateSerial(YR, 1, 1)
'Use ISO weeknumber system
I = DatePart("ww", DY1, vbSunday, vbFirstFourDays)

'Sunday of Week 1
Wk1DT1 = DateAdd("d", -Weekday(DY1), DY1 + 1 + IIf(I > 1, 7, 0))

WNtoDate = DateAdd("ww", WN - 1, Wk1DT1) + DOW - 1

End Function

---

Durno

Answer 4

This is for people like me without a lot of coding experience.

Sub DatesForWeekNum()
Dim WeekNumYear As Integer
    WeekNumYear = ActiveSheet.Range("C14").Value

Dim WeekNum As Integer
    WeekNum = ActiveSheet.Range("C13").Value

Dim StartDateYear As Date
    StartDateYear = DateSerial(WeekNumYear, 1, 1)

Dim WeekDayIndex As Integer
    WeekDayIndex = (StartDateYear - 1) Mod 7 'If your week starts on Sunday
    'WeekDayIndex = (StartDateYear - 2) Mod 7 'If your week starts on Monday

Dim DateOfFirstSunday As Date
    If WeekDayIndex < 4 Then
        DateOfFirstSunday = StartDateYear - WeekDayIndex
    Else
        DateOfFirstSunday = StartDateYear - WeekDayIndex + 7
    End If

Dim StartDateWeekNum As Date
    StartDateWeekNum = DateOfFirstSunday + ((WeekNum - 1) * 7)

Dim EndDateWeekNum As Date
    EndDateWeekNum = DateOfFirstSunday + ((WeekNum - 1) * 7) + 7
MsgBox StartDateWeekNum & vbCrLf & EndDateWeekNum End Sub

Yokoyama