Find First Weekday (Mon) From Week Number

[snakehips2000]

I picked up the following two functions as a means of determining a date from a week number in Excel VBA. Unfortunately, when used together, they return the same date for both week 53 of 2010 and week 1 of 2011 (3 Jan).

Does anyone have anything more reliable please?

Thanks.

Public Function YearStart(WhichYear As Integer) As Date

    Dim WeekDay As Integer
    Dim NewYear As Date
    
    NewYear = DateSerial(WhichYear, 1, 1)
    WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0
    
    If WeekDay < 4 Then
        YearStart = NewYear - WeekDay
    Else
        YearStart = NewYear - WeekDay + 7
    End If

End Function

Public Function WeekStart(WhichWeek As Integer, WhichYear As _
                    Integer) As Date

    WeekStart = YearStart(WhichYear) + ((WhichWeek) * 7)

End Function

 

[MajP]

How are you using the functions, and what is your definition of a week (buisness rules)?

Is the first "week" of 2011: saturday (1 Jan) - friday 7 jan
Or is it :Saturday (1 Jan), and week 2 is the traditional (sun - sun)

As you know there are fifty-two (52) complete weeks in a (Julian) year plus an extra day, or two in a leap year. So a year will contain 52 1/7 or 52 2/7 weeks.

The days on the calendar will be arranged in 53 weeks, at least one of which will have less than 7 days. In the rarest of cases, the calendar can overlap 54 weeks in one year : January 1 and December 31 in their own separate weeks.
So kind of depends on your definitions.

 

[SkipVought]

Hi,

Your SECOND function should be modified...

WeekStart = YearStart(WhichYear) + ((WhichWeek[b] - 1[/b]) * 7)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[snakehips2000]

MajP - sorry if there's any confusion on this. In the subject line of the thread I tried to state that I'm looking to return the date of the first day (assuming the week runs from Monday to Sunday) for the stated week. For example, week 1 in 2011 would return 3 Jan. Likewise, week 53 for 2010 should return 27 Dec.

SkipVought - my error in the coding I pasted for the second function (WeekStart) in that it should have read as you stated, i.e.

WeekStart = YearStart(WhichYear) + ((WhichWeek - 1) * 7)

That said, it still returns the same date for both week 53 of 2010 and week 1 of 2011 (3 Jan). Thus, I'm still no nearer a resolution for it.

 

[SkipVought]

week 52 of 2010 is 1/3/2011

week 53 of 2010 is 1/10/2011

So what's the problem?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

Here we go ...

 

[MajP]

The problem is that by your definition there is no week 53 in 2010, because I guess if the week does not start on a monday it becomes week 0.

so then

2010
week 0                           01/01 01/02 01/03
week 1   01/04 01/05 01/06 01/07 01/08 01/09 01/10
...
week 52  12/27 12/28 12/29 12/30 12/31 
2010 no week 53

2011
week 0                                01/01 01/02
week 1  01/03 01/04 01/05 01/06 01/07 01/08 01/09

so week 53 is a bad input and you need to check for that

Public Function WeekStart(WhichWeek As Integer, WhichYear As _
                    Integer) As Date
      WeekStart = YearStart(WhichYear) + ((WhichWeek - 1) * 7)
    If Year(WeekStart) > WhichYear Then
      MsgBox "You exceeded the amount of weeks"
      WeekStart = 0
    ElseIf Year(WeekStart) < WhichYear Then
      MsgBox "You can not use a negative amount of weeks"
      WeekStart = 0
    Else
      WeekStart = YearStart(WhichYear) + ((WhichWeek - 1) * 7)
    End If
End Function

 

[snakehips2000]

Thanks guys for the update and the revised function coding. I guess I started off with the wrong assumption, i.e. that there were 53 weeks in 2010 when in fact there were only 52.