Convert week number to a date

[Guest]

I have data in a table in the form of a week number but I want to change it
to a week date. That is, for example, I would like to change the week number
35 to the Saturday of that week, or 9/1. I know where I can get the year to
concatenate it so I get 9/1/2007, but I'm not clear on how I can do the first
part of the conversion. Can anyone help?

 

[Pieter Wijnen]

This is a start & I Think it works

Public Function Week2Date(WeekNo As Long) As Date
Dim Jan1 As Date
Dim Sub1 As Boolean
Dim Ret As Date

Jan1 = VBA.DateSerial(VBA.Year(VBA.Date()), 1, 1)
'Sub1 = (VBA.Format(Jan1, "ww", VBA.vbMonday, VBA.vbFirstFourDays) = 1)
Sub1 = (VBA.Format(Jan1, "ww", VBA.vbUseSystem, VBA.vbUseSystem) = 1)
Ret = VBA.DateAdd("ww", WeekNo + Sub1, Jan1)
Ret = Ret - VBA.Weekday(Ret) + 7
Week2Date = Ret
End Function



HtH

Pieter

 

[Guest]

Thanks Pieter, can you please explain a little more of what's going on in
this function? It's not returning what I expect. My week numbers should start

 

[Pieter Wijnen]

Is your regional setting set up to use saturday as the first weekday?
you can specify explicitly in the WeekDay statement
& there's bugs in my posted code <g>

the modified function is:

Public Function Week2Date(WeekNo As Long, Optional ByVal Yr As Long = 0, _
Optional ByVal DOW As VBA.VbDayOfWeek =
VBA.VbDayOfWeek.vbUseSystemDayOfWeek, _
Optional ByVal FWOY As VBA.VbFirstWeekOfYear =
VBA.VbFirstWeekOfYear.vbUseSystem) As Date
' Returns First Day of week
Dim Jan1 As Date
Dim Sub1 As Boolean
Dim Ret As Date

If Yr = 0 Then
Jan1 = VBA.DateSerial(VBA.Year(VBA.Date()), 1, 1)
Else
Jan1 = VBA.DateSerial(Yr, 1, 1)
End If
Sub1 = (VBA.Format(Jan1, "ww", DOW, FWOY) = 1)
Ret = VBA.DateAdd("ww", WeekNo + Sub1, Jan1)
Ret = Ret - VBA.Weekday(Ret, DOW) + 1
Week2Date = Ret
End Function


Pieter

 

[Pieter Wijnen]

There's bugs in my posted code

Public Function Week2Date(WeekNo As Long, Optional ByVal Yr As Long = 0, _
Optional ByVal DOW As VBA.VbDayOfWeek =
VBA.VbDayOfWeek.vbUseSystemDayOfWeek, _
Optional ByVal FWOY As VBA.VbFirstWeekOfYear =
VBA.VbFirstWeekOfYear.vbUseSystem) As Date
' Returns First Day of week
Dim Jan1 As Date
Dim Sub1 As Boolean
Dim Ret As Date

If Yr = 0 Then
Jan1 = VBA.DateSerial(VBA.Year(VBA.Date()), 1, 1)
Else
Jan1 = VBA.DateSerial(Yr, 1, 1)
End If
Sub1 = (VBA.Format(Jan1, "ww", DOW, FWOY) = 1)
Ret = VBA.DateAdd("ww", WeekNo + Sub1, Jan1)
Ret = Ret - VBA.Weekday(Ret, DOW) + 1
Week2Date = Ret
End Function


HtH

Pieter