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
--
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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jay" <(E-Mail Removed)> wrote in message news:731CC4FB-B58D-42E5-9FC8-(E-Mail Removed)...
> 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
> --
> Disregard, this is so I can find my post later.
> ***postedbyJay***

What is your definition of week number.

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

--
Regards,
Tom Ogilvy




"Jay" wrote:

> 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
> --
> Disregard, this is so I can find my post later.
> ***postedbyJay***

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

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


"Tom Ogilvy" wrote:

> What is your definition of week number.
>
> http://www.cpearson.com/excel/weeknum.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Jay" wrote:
>
> > 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
> > --
> > Disregard, this is so I can find my post later.
> > ***postedbyJay***

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***


"Ron de Bruin" wrote:

> If I understand you correct ?
>
> With my filter Add-in EasyFilter you can filter on weeks
> http://www.rondebruin.nl/easyfilter.htm
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Jay" <(E-Mail Removed)> wrote in message news:731CC4FB-B58D-42E5-9FC8-(E-Mail Removed)...
> > 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
> > --
> > Disregard, this is so I can find my post later.
> > ***postedbyJay***
>

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jay" <(E-Mail Removed)> wrote in message news:283CBC88-C47D-4B91-A33D-(E-Mail Removed)...
> 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
>
> --
> Disregard, this is so I can find my post later.
> ***postedbyJay***
>
>
> "Tom Ogilvy" wrote:
>
>> What is your definition of week number.
>>
>> http://www.cpearson.com/excel/weeknum.htm
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>>
>>
>> "Jay" wrote:
>>
>> > 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
>> > --
>> > Disregard, this is so I can find my post later.
>> > ***postedbyJay***

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.

--
Regards,
Tom Ogilvy


"Jay" wrote:

> 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
>
> --
> Disregard, this is so I can find my post later.
> ***postedbyJay***
>
>
> "Tom Ogilvy" wrote:
>
> > What is your definition of week number.
> >
> > http://www.cpearson.com/excel/weeknum.htm
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> > "Jay" wrote:
> >
> > > 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
> > > --
> > > Disregard, this is so I can find my post later.
> > > ***postedbyJay***

Tom,

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

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


"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Jay" wrote:
>
> > 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
> >
> > --
> > Disregard, this is so I can find my post later.
> > ***postedbyJay***
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > What is your definition of week number.
> > >
> > > http://www.cpearson.com/excel/weeknum.htm
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > >
> > > "Jay" wrote:
> > >
> > > > 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
> > > > --
> > > > Disregard, this is so I can find my post later.
> > > > ***postedbyJay***

Hi Jay

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jay" <(E-Mail Removed)> wrote in message news:A18BDA79-927B-4AA7-8797-(E-Mail Removed)...
> Tom,
>
> Still working on understanding it, but the end result works well. Does what
> I need.
>
> Thanks,
> Jay
> --
> Disregard, this is so I can find my post later.
> ***postedbyJay***
>
>
> "Tom Ogilvy" wrote:
>
>> 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.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Jay" wrote:
>>
>> > 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
>> >
>> > --
>> > Disregard, this is so I can find my post later.
>> > ***postedbyJay***
>> >
>> >
>> > "Tom Ogilvy" wrote:
>> >
>> > > What is your definition of week number.
>> > >
>> > > http://www.cpearson.com/excel/weeknum.htm
>> > >
>> > > --
>> > > Regards,
>> > > Tom Ogilvy
>> > >
>> > >
>> > >
>> > >
>> > > "Jay" wrote:
>> > >
>> > > > 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
>> > > > --
>> > > > Disregard, this is so I can find my post later.
>> > > > ***postedbyJay***

> 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