Converting WeekNumber to Weekday
 

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

Converting WeekNumber to Weekday
 

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

Converting WeekNumber to Weekday
 

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

Converting WeekNumber to Weekday
 

On Wed, 19 Sep 2007 09:24:01 -0700, 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

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

Converting WeekNumber to Weekday
 

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

Converting WeekNumber to Weekday
 

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

Converting WeekNumber to Weekday
 

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

Converting WeekNumber to Weekday
 

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

Converting WeekNumber to Weekday
 

Thank you Ron.

"Ron Rosenfeld" wrote:

On Wed, 19 Sep 2007 09:24:01 -0700, 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

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

Converting WeekNumber to Weekday
 

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

Converting WeekNumber to Weekday
 

Thank you Rick.

"Rick Rothstein (MVP - VB)" wrote:

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