Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calendar with weeknumber? | Excel Programming | |||
=today() = weeknumber | Excel Worksheet Functions | |||
WEEKDAY() function: display TEXT not numeric weekday | Excel Discussion (Misc queries) | |||
weeknumber | Excel Discussion (Misc queries) | |||
Weeknumber in Excel | Excel Discussion (Misc queries) |