Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
That sounds like the ISO Week. So based on the page I gave you
Function YearStart(WhichYear As Integer) As Date Dim WeekDay As Integer Dim NewYear As Date NewYear = DateSerial(WhichYear, 1, 1) WeekDay = (NewYear - 2) Mod 7 If WeekDay < 4 Then YearStart = NewYear - WeekDay Else YearStart = NewYear - WeekDay + 7 End If End Function will give you the date of Monday in the first week. You would then mutilply 7 times the week number minus 1 and add it to that date to get the Monday of your week. If I haven't made an error, here is a slightly different algorithm for getting the start of year, coupled with the week number calculation, which yields this function for the Monday of the week number specified... Function StartOfWeek(TheYear As Long, WeekNumber As Long) As Date Dim DayOne As Date DayOne = "1/1/" & CStr(TheYear) If DatePart("ww", DayOne, vbMonday, vbFirstFourDays) 1 Then DayOne = DayOne + 7 End If StartOfWeek = DayOne - Weekday(DayOne, vbTuesday) + 7 * WeekNumber End Function Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
Display "this week" column headers w/date & day of week? | Excel Worksheet Functions | |||
Check if Date within this week/last week | Excel Programming | |||
from date return week date range | Excel Worksheet Functions | |||
I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) |