Thread: WeekNum Trouble
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
VP VP is offline
external usenet poster
 
Posts: 1
Default WeekNum Trouble

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)
"Brad" wrote:

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?