VBA looping range of cells then Get a Day from a WeekNumber

[geloader0]

Hi Good day, I want to convert the Weeknumber to a date example from Week 1(start from Sunday), Month 1(January) then year. So the output goes like this " 01/01/2023 - 01/07/2023 "
Please see example below... Thank you very much

Convert Weeknumber from this

To this.

 

[Rick Rothstein]

Your description says you want Week 1 to start on Sunday, but your table shows Week 2 in 2022 starting on Saturday. This raises two questions...

1) Does Week 1 start on Sunday or the 1st of January?

2) If it starts on Sunday, what should be shown for Week 1 in 2022 which, for that assumption, would actually be 12/26/2021-1/1/2022?

 

[geloader0]

Your description says you want Week 1 to start on Sunday, but your table shows Week 2 in 2022 starting on Saturday. This raises two questions...

1) Does Week 1 start on Sunday or the 1st of January?

2) If it starts on Sunday, what should be shown for Week 1 in 2022 which, for that assumption, would actually be 12/26/2021-1/1/2022?

Hi! Sorry, I was mistake the Days always starts from Sunday. So, for the Week 2 of 2022 it must be 01/09/2022 - 01/15/2022
#1. The Week 1 start from 1st of January and the day start from Sunday to Saturday
#2. I didn't notice that there is only 1 day in Week 1 in 2022. Or maybe the Week 1 in 2022 will be 01/02/2022 - 01/08/2022 , If 12/26/2021 - 01/01/2022 is difficult to get.

 

[Rick Rothstein]

#2. I didn't notice that there is only 1 day in Week 1 in 2022. Or maybe the Week 1 in 2022 will be 01/02/2022 - 01/08/2022 , If 12/26/2021 - 01/01/2022 is difficult to get.

I can get either one of those for Week 1, you just have to tell me (us) which is the one you want.

 

[geloader0]

I can get either one of those for Week 1, you just have to tell me (us) which is the one you want.

That would be great. I want 01/02/2022 - 01/08/2022

 

[geloader0]

I can get either one of those for Week 1, you just have to tell me (us) which is the one you want.

Anyway, I found this code to somewhere

THE FUNCTION to get the date within that weeknumber and year range

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

This code will give me the exact date I want in Week 2 of 2022 then adding 6days to the value

Sub TEST()
Msgbox Format(GetDayFromWeekNumber(Range("B3"), Range("A3"), 7), "MM/dd/yyyy") & " - " & Format(GetDayFromWeekNumber(Range("B3"), Range("A3"), 7) + 6, "MM/dd/yyyy")
End Sub

Now, I want to loop through cell range in column A and B