Converting WeekNumber to Weekday

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
Click to expand...

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
 
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.
Click to expand...

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 said:
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
Click to expand...
 
Thanks Mike this works good to.

Mike H said:
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
Click to expand...
 
Thank you Ron.

Ron Rosenfeld said:
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
Click to expand...
 
Thank you Rick.

Rick Rothstein (MVP - VB) said:
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
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can some one modify the thirdfriday code to get me the following d 2
Weeknumbers 4
displaying "week of July 29, 2007" 2
sumif weekday 3
Convert WEEKNUM(date;2) to text "Monday 25th - Sunday 31st October 4
Weeknumbers 7
Convert WEEKNUM(date;2) to text "Monday 25th - Sunday 31st Oct" 3
function for date of first and last day through weeknumber 1

Back
Top