WeekNum Trouble

[Brad]

I've wrote a function to take an integer representing the Calendar Week and
return the date of the monday of that week. A reverse of the weeknum
function. Works fine when I paste the formula in a worksheet, but I get a
compile error in vba. Here's the code.

Function GetDate(iCalWeek As Integer) As Date
'
' Takes calendar week num as an argument and returns the date of monday of
that week
'
Dim sSunday As Date

sSunday = Date(Year(Today()), 1, 1) + _
(7 - Weekday(Date(Year(Today()), 1, 1)) - 6) + (iCalWeek * 7)

GetDate = sSunday + 1

End Function

any ideas?

 

[Dave Peterson]

Use DateSerial instead of date().
Use Date instead of Today()

Both =date() and =today() are worksheet functions.

 

[Rick Rothstein]

I think this function does what you want...

Function GetDate(iCalWeek As Integer) As Date
GetDate = DateSerial(Year(Now), 1, 7 * (iCalWeek - 1) + 1)
GetDate = GetDate - Weekday(GetDate) + 2
End Function

 

[Brad]

Thanks!

Use DateSerial instead of date().
Use Date instead of Today()

Both =date() and =today() are worksheet functions.

 

[VP]

Here is my take on this -

Function GetDate(iCalWeek As Integer) As Date
'
' Takes calendar week num as an argument and returns the date of monday of

'
If (iCalWeek > 0 And iCalWeek <= 52) Then
Else
MsgBox ("Please enter a valid week")
Exit Function
End If

MyDate = DateSerial(Year(Date), 1, 1)
' Assign a date.
MyWeekDay = Weekday(MyDate) ' Get the day for First of the year

If (MyWeekDay > 2 And iCalWeek = 1) Then
MsgBox ("There was no Monday in the first week")
Exit Function
End If
'TODO: Add another validation for the number 52, if there is no monday
in the 52nd week etc

Dim tempDate As Date
If (MyWeekDay >= 2) Then
tempDate = DateAdd("ww", iCalWeek - 1, MyDate)
Else
tempDate = DateAdd("ww", iCalWeek, MyDate)
End If


Dim sMonday As Date
' To get the Monday for the current week, use this
'sMonday = DateAdd("d", -Weekday(Date) + 2, Date)

sMonday = DateAdd("d", -Weekday(tempDate) + 2, tempDate)
GetDate = sMonday
End Function


Cheers,
Varun
(Sydney)