Week number to date??

[Mal]

Hello,

Any advice on a function to convert a given week number to a date?
Ideally I'd like the first day of that week.

I'm using this to compare year to year....using the week number as the
common factor.

Mal.

[Wayne Gillespie]

On Sun, 08 Jan 2006 03:59:55 GMT, "Mal" <[e-mail] t> wrote:

Hello,

Any advice on a function to convert a given week number to a date?
Ideally I'd like the first day of that week.

I'm using this to compare year to year....using the week number as the
common factor.

Mal.

*not thoroughly tested

Function fGetWeekDate(in tWeekNo As Integer, _
Optional intYear As Integer, _
Optional intStartDay As Integer = vbSunday) As Date

Dim dtCheckDate As Date

If intYear = 0 Then intYear = Year(Date)

'get first (intStartDay) of year
dtCheckDate = CDate("1/1/" & intYear)
Do Until WeekDay(dtCheck Date, intStartDay) = intStartDay
dtCheckDate = dtCheckDate + 1
Loop

'adjust if first (intStartDay) of year is week 52 or 53
If DatePart("ww", dtCheckDate, , vbFirstFourDays ) >= 52 Then
dtCheckDate = DateAdd("ww", 1, dtCheckDate)
End If

fGetWeekDate = DateAdd("ww", intWeekNo - 1, dtCheckDate)

End Function
Wayne Gillespie
Gosford NSW Australia

[Terry Kreft]

This is not as easy as it first appears. It depends on how your week number
is defined, or rather how the week numbering start is defined.

Possible definitions I can think of are:-
Week 1 is the week in which the 1st of January falls
Week 1 is the first full week of the year
Week 1 is the first week of the year which contains four days of the
year.

This is then compounded by how do you define the first day of the week.

IIRC the ISO standard is that Monday is the first day of the week and the
first week of the year is the first week which contains four days of the
year.

In short until you define how your week number and first day of the week are
defined it's not possible to answer the question.
--
Terry Kreft

"Mal" <[e-mail] t> wrote in message
news:%6******** *********@newsr ead3.news.atl.e arthlink.net...

Hello,

Any advice on a function to convert a given week number to a date?
Ideally I'd like the first day of that week.

I'm using this to compare year to year....using the week number as the
common factor.

Mal.

[salad]

Mal wrote:

Hello,

Any advice on a function to convert a given week number to a date?
Ideally I'd like the first day of that week.

I'm using this to compare year to year....using the week number as the
common factor.

Mal.

Expanding a bit on what others wrote, you can view these results in the
Immediate/Debug window
? format(#1/1/2006#,"ww",vbMo nday,vbFirstFou rDays)
52
? format(#1/1/2006#,"ww",vbMo nday,vbFirstFul lWeek)
52
? format(#1/1/2006#,"ww",vbMo nday,vbFirstJan 1)
1
? format(#1/1/2006#,"ww",vbMo nday,vbUseSyste m)
1
? weekday(#1/1/2006#,vbSunday)
1
? weekday(#1/1/2006#,vbMonday)
7

1/1/2006 is a Sunday. vbSunday is the default in the Weekday function.
Which day is your default? Sunday, Monday...Saturd ay?

I guess you'll need to find out what the week number is for the first of
the year. Then if the week day is not the start week day either
increment or decrement the days till you get your start day from the
first of the year. From there you can multiply the number of (weeks-1)
* 7. If the week is 52, you'd want to set the week to 0.