Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WeekNum Trouble
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WeekNum Trouble
Use DateSerial instead of date().
Use Date instead of Today() Both =date() and =today() are worksheet functions. 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? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
WeekNum Trouble
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 -- Rick (MVP - Excel) "Brad" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
WeekNum Trouble
Thanks!
"Dave Peterson" wrote: Use DateSerial instead of date(). Use Date instead of Today() Both =date() and =today() are worksheet functions. 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? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weeknum | Excel Discussion (Misc queries) | |||
=WEEKNUM | Excel Programming | |||
Weeknum help | Excel Worksheet Functions | |||
WEEKNUM() | Excel Discussion (Misc queries) | |||
WeekNum Trouble | Excel Programming |