Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,202
Default Date Range of Week

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert regular Date to Week Ending or Week Beginning Dates Sam H Excel Discussion (Misc queries) 5 April 3rd 23 04:39 PM
Display "this week" column headers w/date & day of week? Ivan Wiegand Excel Worksheet Functions 9 September 12th 07 05:18 PM
Check if Date within this week/last week Duncan[_5_] Excel Programming 4 December 15th 06 02:37 PM
from date return week date range ERahn Excel Worksheet Functions 3 December 2nd 06 02:28 AM
I need week number in excell from a date, first week must be mini. jPeich Excel Discussion (Misc queries) 4 January 5th 05 01:21 AM


All times are GMT +1. The time now is 06:28 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"