I s it possible to use the week number and calculate the date and day of the
week. For example, Week number 38 would calculate to Monday, September 17.

Thanks

You can give this a whirl... Use it like

=BOWeek(38, 2007)
to return sept 17, 2007

Function VBAWeekNum(D As Date, FW As Integer) As Integer
'Code by Chip Pearson
VBAWeekNum = CInt(Format(D, "ww", FW))
End Function

Public Function BOWeek(ByVal lngWeek As Long, ByVal lngYear As Long) As Date
Dim lng As Long
Dim lngDaysInWeek1 As Long

For lng = 1 To 7
If VBAWeekNum(DateSerial(lngYear, 1, lng), 1) = 2 Then Exit For
lngDaysInWeek1 = lngDaysInWeek1 + 1
Next lng
BOWeek = DateSerial(lngYear, 1, 1) - (7 - lngDaysInWeek1) + lngWeek * 7
- 6

End Function
--
HTH...

Jim Thomlinson


"JRForm" wrote:

> I s it possible to use the week number and calculate the date and day of the
> week. For example, Week number 38 would calculate to Monday, September 17.
>
> Thanks

Or as a worksheet function:-

date in A1
weeknum in b
=DATE(YEAR(A1),1,B1*7-(DAY(DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1))))

Mike

"JRForm" wrote:

> I s it possible to use the week number and calculate the date and day of the
> week. For example, Week number 38 would calculate to Monday, September 17.
>
> Thanks

On Wed, 19 Sep 2007 09:24:01 -0700, JRForm <(E-Mail Removed)>
wrote:

>I s it possible to use the week number and calculate the date and day of the
>week. For example, Week number 38 would calculate to Monday, September 17.
>
>Thanks

How are you calculating your week number?

If you are using the Excel WEEKNUM worksheet function, with it's default start
of Sunday for the beginning of the week, then the Monday of that week would be:

=DATE(YEAR(Today()),1,-5)+7*weeknum-WEEKDAY(DATE(YEAR(Today()),1,0))

If you are calculating Week Number using some other method, you'll have to post
that here.
--ron

Seems to me you would need to define how you are calculating your week number
first.

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

--
Regards,
Tom Ogilvy



"JRForm" wrote:

> I s it possible to use the week number and calculate the date and day of the
> week. For example, Week number 38 would calculate to Monday, September 17.
>
> Thanks

> Is it possible to use the week number and calculate the date and day of
> the
> week. For example, Week number 38 would calculate to Monday, September
> 17.

If the week number is in A1 and the year is in B1, then this formula will
yield September 17, 2007 as you ask for...

=DATE(B1,1,1+7*(A1-1))

Rick

Thank you Jim this works great!

"Jim Thomlinson" wrote:

> You can give this a whirl... Use it like
>
> =BOWeek(38, 2007)
> to return sept 17, 2007
>
> Function VBAWeekNum(D As Date, FW As Integer) As Integer
> 'Code by Chip Pearson
> VBAWeekNum = CInt(Format(D, "ww", FW))
> End Function
>
> Public Function BOWeek(ByVal lngWeek As Long, ByVal lngYear As Long) As Date
> Dim lng As Long
> Dim lngDaysInWeek1 As Long
>
> For lng = 1 To 7
> If VBAWeekNum(DateSerial(lngYear, 1, lng), 1) = 2 Then Exit For
> lngDaysInWeek1 = lngDaysInWeek1 + 1
> Next lng
> BOWeek = DateSerial(lngYear, 1, 1) - (7 - lngDaysInWeek1) + lngWeek * 7
> - 6
>
> End Function
> --
> HTH...
>
> Jim Thomlinson
>
>
> "JRForm" wrote:
>
> > I s it possible to use the week number and calculate the date and day of the
> > week. For example, Week number 38 would calculate to Monday, September 17.
> >
> > Thanks

Thanks Mike this works good to.

"Mike H" wrote:

> Or as a worksheet function:-
>
> date in A1
> weeknum in b1
> =DATE(YEAR(A1),1,B1*7-(DAY(DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1))))
>
> Mike
>
> "JRForm" wrote:
>
> > I s it possible to use the week number and calculate the date and day of the
> > week. For example, Week number 38 would calculate to Monday, September 17.
> >
> > Thanks

Thank you Ron.

"Ron Rosenfeld" wrote:

> On Wed, 19 Sep 2007 09:24:01 -0700, JRForm <(E-Mail Removed)>
> wrote:
>
> >I s it possible to use the week number and calculate the date and day of the
> >week. For example, Week number 38 would calculate to Monday, September 17.
> >
> >Thanks
>
> How are you calculating your week number?
>
> If you are using the Excel WEEKNUM worksheet function, with it's default start
> of Sunday for the beginning of the week, then the Monday of that week would be:
>
> =DATE(YEAR(Today()),1,-5)+7*weeknum-WEEKDAY(DATE(YEAR(Today()),1,0))
>
> If you are calculating Week Number using some other method, you'll have to post
> that here.
> --ron
>

Thank you Tom.

"Tom Ogilvy" wrote:

> Seems to me you would need to define how you are calculating your week number
> first.
>
> http://www.cpearson.com/excel/weeknum.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "JRForm" wrote:
>
> > I s it possible to use the week number and calculate the date and day of the
> > week. For example, Week number 38 would calculate to Monday, September 17.
> >
> > Thanks