Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
start/end dates of a week given just the weeknum() value
Hi
I see weeknum() can return a week number given a date. However I need to reverse this procedure and calculate the START and END dates for a week given just the weeknumber [weeknum() ] for the week concerned for example, given week number 50 I need to return Dec 5 (start) and Dec 11 (end) week number 51 I need to return Dec 12 (start) and Dec 18 (end) thanks , David |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
start/end dates of a week given just the weeknum() value
Maybe this..........
With your weeknumber in A1, This in B1 =TEXT(38353+(A1*7)-7,"mm/dd/yy") This in C1 =TEXT(38353+(A1*7)-1,"mm/dd/yy") Vaya con Dios, Chuck, CABGx3 "David" wrote: Hi I see weeknum() can return a week number given a date. However I need to reverse this procedure and calculate the START and END dates for a week given just the weeknumber [weeknum() ] for the week concerned for example, given week number 50 I need to return Dec 5 (start) and Dec 11 (end) week number 51 I need to return Dec 12 (start) and Dec 18 (end) thanks , David |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
start/end dates of a week given just the weeknum() value
David,
For the start date: =(A2-2)*7+DATEVALUE("Jan 3, 2005") end date: =(A2-2)*7+DATEVALUE("Jan 3, 2005") + 6 These are hard-coded for year 2005. The jan 3 date is the date on which week 2 starts for the year of interest. To make this work over various years will take more. Earl Kiosterud www.smokeylake.com "David" wrote in message ... Hi I see weeknum() can return a week number given a date. However I need to reverse this procedure and calculate the START and END dates for a week given just the weeknumber [weeknum() ] for the week concerned for example, given week number 50 I need to return Dec 5 (start) and Dec 11 (end) week number 51 I need to return Dec 12 (start) and Dec 18 (end) thanks , David |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
start/end dates of a week given just the weeknum() value
thanks Earl
"Earl Kiosterud" wrote: David, For the start date: =(A2-2)*7+DATEVALUE("Jan 3, 2005") end date: =(A2-2)*7+DATEVALUE("Jan 3, 2005") + 6 These are hard-coded for year 2005. The jan 3 date is the date on which week 2 starts for the year of interest. To make this work over various years will take more. Earl Kiosterud www.smokeylake.com "David" wrote in message ... Hi I see weeknum() can return a week number given a date. However I need to reverse this procedure and calculate the START and END dates for a week given just the weeknumber [weeknum() ] for the week concerned for example, given week number 50 I need to return Dec 5 (start) and Dec 11 (end) week number 51 I need to return Dec 12 (start) and Dec 18 (end) thanks , David |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
start/end dates of a week given just the weeknum() value
thanks Chuck
"CLR" wrote: Maybe this.......... With your weeknumber in A1, This in B1 =TEXT(38353+(A1*7)-7,"mm/dd/yy") This in C1 =TEXT(38353+(A1*7)-1,"mm/dd/yy") Vaya con Dios, Chuck, CABGx3 "David" wrote: Hi I see weeknum() can return a week number given a date. However I need to reverse this procedure and calculate the START and END dates for a week given just the weeknumber [weeknum() ] for the week concerned for example, given week number 50 I need to return Dec 5 (start) and Dec 11 (end) week number 51 I need to return Dec 12 (start) and Dec 18 (end) thanks , David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Xmas Week Dates | Excel Discussion (Misc queries) | |||
weeknum function says jan1=week1, mod to first 4 day week needed | Excel Discussion (Misc queries) | |||
user defined function | Excel Worksheet Functions | |||
Rouding Dates to beginning of a week | Excel Discussion (Misc queries) | |||
How do I ensure dates inputted are during the work week? | Excel Worksheet Functions |