first day of the week

I will fill in a weeknumber and i must receive the date of the first day of this week.
for example:
I type week 46 and he returns me 15/11/99

How can i do that?

Thanks

Nicnat - well, this works in Excel VBA and should (I hope) work in VB5 etc.

Sub Fred()
Dim WeekToDo As Integer
'
WeekToDo = 46
MsgBox FirstDay(WeekToDo)
End Sub

Function FirstDay(WeekNo As Integer) As Date
Dim OneJan, DayOneJan
'
WeekNo = WeekNo + 1 'Because you seem to be ignoring the first Friday/Saturday of the year
OneJan = Format("01/01/99", "#####") 'Get serial of first day of year
DayOneJan = Format(OneJan, "w") ' Get Weekday number (1 is Sunday) of first day of year
'
FirstDay = ((WeekNo * 7) + (OneJan - DayOneJan)) 'Calculate last day of week (ie Saturday)
FirstDay = FirstDay - 5 ' Gets you back to Monday
FirstDay = Format(FirstDay, "dd/mm/yy") ' Reformat to a date
End Function

Sure thing:

Code:

---

Public Function GetFirstWeekDayFromWeek(pWeekNumber As Integer) As Date
    Dim dtTemp As Date
    Dim d As Date
   
    dtTemp = DateAdd("ww", pWeekNumber, "1/1/" & Year(Date))
    GetFirstWeekDayFromWeek = dtTemp - Weekday(dtTemp) + 2 'Because the default start weekday is Sunday
End Function

---

Usage: GetFirstWeekDayFromWeek WeekNumber

Example: MsgBox GetFirstWeekDayFromWeek(46)
The result would be first day of that week (11/15/99)

Note: This function will calculate the weeknumber according to the current year. If you want to change it to except any year, you can use this modified function:

Code:

---

Public Function GetFirstWeekDayFromWeek(pWeekNumber As Integer, pYear As Integer) As Date
    Dim dtTemp As Date
    Dim d As Date
   
    If Len(CStr(pYear)) <> 4 Then
        MsgBox "Not valid year."
        Exit Function
    End If
    dtTemp = DateAdd("ww", pWeekNumber, "1/1/" & pYear)
    GetFirstWeekDayFromWeek = dtTemp - Weekday(dtTemp) + 2 'Because the default start weekday is Sunday
End Function

---

Example: MsgBox GetFirstWeekDayFromWeek(46, 2000)
The result would be first day of that week (11/13/00)


------------------

Serge

Software Developer
[email protected]
[email protected]
ICQ#: 51055819


[This message has been edited by Serge (edited 11-19-1999).]