Date Range of Week

[Guest]

Hello all,

I've searched through the posts, but I can't seem to find an answer so I'm
posting my question.

I'm looking for a way to determine the date range (first and last day) of a
week when given the week number.

So if I'm given week 25 of 2007, I would find the first day is June 18, 2007
to June 24, 2007 (assuming first day of the week is Monday).

All I can think of is to count up from Jan. 01, 2007 and if it isn't the
right week number, keep counting up until I find the first date that matches
the week number.

Just curious is anyone knows of an existing (or more elegant) way to
determine the date range of a week.

Any suggestions are appreciated.

Thanks,
Jay

 

[Ron de Bruin]

If I understand you correct ?

With my filter Add-in EasyFilter you can filter on weeks
http://www.rondebruin.nl/easyfilter.htm

 

[Guest]

What is your definition of week number.

http://www.cpearson.com/excel/weeknum.htm

 

[Guest]

Definition... hadn't thought about that,.

Week is a number between 1 and 53, with the first day of the week being
Monday and the first week being the first week with 4 or more days in it.

Hope that answers the question...

I'm trying to write a function that gets a week number and a year and can
determine the first and last day of the week (outputting a string).

If I use DatePart, I can set those options and get a week number. I can
compare it to the week number I passed into the function, if it's the same,
then I have the first day of the week. If not, add 1 day. Check again.

I was hoping there was a better way to do it. This process seems (to me
anyway) unnecessarily complicated. I thought if someone knew of an existing
function that did the same thing, then I might try it.

Hope that answers the question.

Cheers,
Jay

 

[Guest]

Thanks for the response Ron.

Not quite what I need. I'm trying to get the date range in a function, not
in a worksheet.

Given a week number (0 < week <= 53) and a year, determine the first and
last day of the week.

I replied to Tom with a more complete answer in regards to what I'm trying
to accomplish.

Hope it helps.

Thanks,
Jay

--
Disregard, this is so I can find my post later.
***postedbyJay***

If I understand you correct ?

With my filter Add-in EasyFilter you can filter on weeks
http://www.rondebruin.nl/easyfilter.htm

 

[Ron de Bruin]

For ISO week numbers see
http://www.rondebruin.nl/isodate.htm

 

[Guest]

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.

 

[Guest]

Tom,

Still working on understanding it, but the end result works well. Does what
I need.

Thanks,
Jay

 

[Ron de Bruin]

Hi Jay

On my page you find also a worksheet function to do this

 

[Rick Rothstein \(MVP - VB\)]

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