Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a week between a range of dates
Hi,
I have a little table which looks up a period number, and also has 4 columns for the week numbers of the period. Example Period week 1 week 2 week 3 week4 P1 05/04/10 12/04/10 19/04/10 26/04/10 P2 03/05/10 10/05/10 17/05/10 24/05/10 I have worked out a simple formala, to say if todays date is 13/04/10, it will return period P1. (as its between 05/04/10 - 03/05/10) What i am trying to do, is to now work out the week number , which should be week 2 (as its between 12/04/10 - 19/04/10) Any help fully appriciated. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a week between a range of dates
How about this
=INDEX(B1:E1,MATCH(TODAY()-WEEKDAY(TODAY())+2,INDEX(B1:E3,MATCH(TODAY()-WEEKDAY(TODAY())+2,B:B,1),0),0)) -- HTH Bob "Terry Tibbs" wrote in message ... Hi, I have a little table which looks up a period number, and also has 4 columns for the week numbers of the period. Example Period week 1 week 2 week 3 week4 P1 05/04/10 12/04/10 19/04/10 26/04/10 P2 03/05/10 10/05/10 17/05/10 24/05/10 I have worked out a simple formala, to say if todays date is 13/04/10, it will return period P1. (as its between 05/04/10 - 03/05/10) What i am trying to do, is to now work out the week number , which should be week 2 (as its between 12/04/10 - 19/04/10) Any help fully appriciated. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a week between a range of dates
Thank you,
This seems to have worked. On 13 Apr, 11:18, "Bob Phillips" wrote: How about this =INDEX(B1:E1,MATCH(TODAY()-WEEKDAY(TODAY())+2,INDEX(B1:E3,MATCH(TODAY()-WEE*KDAY(TODAY())+2,B:B,1),0),0)) -- HTH Bob "Terry Tibbs" wrote in message ... Hi, I have a little table which looks up a period number, and also has 4 columns for the week numbers of the period. Example Period *week 1 * * *week 2 * * week 3 * *week4 P1 * * * * 05/04/10 * *12/04/10 * 19/04/10 *26/04/10 P2 * * * * 03/05/10 * *10/05/10 * 17/05/10 *24/05/10 I have worked out a simple formala, to say if todays date is 13/04/10, it will return period P1. (as its between 05/04/10 - 03/05/10) What i am trying to do, is to now work out the week number , which should be week 2 (as its between 12/04/10 - 19/04/10) Any help fully appriciated. Thanks- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a week between a range of dates
On Tue, 13 Apr 2010 02:54:36 -0700 (PDT), Terry Tibbs
wrote: Hi, I have a little table which looks up a period number, and also has 4 columns for the week numbers of the period. Example Period week 1 week 2 week 3 week4 P1 05/04/10 12/04/10 19/04/10 26/04/10 P2 03/05/10 10/05/10 17/05/10 24/05/10 I have worked out a simple formala, to say if todays date is 13/04/10, it will return period P1. (as its between 05/04/10 - 03/05/10) What i am trying to do, is to now work out the week number , which should be week 2 (as its between 12/04/10 - 19/04/10) Any help fully appriciated. Thanks There may be simpler methods. 1. Name your data table Tbl 2. Define a name InnerTbl refers to: =OFFSET(Tbl,1,1,ROWS(Tbl)-1,COLUMNS(Tbl)-1) 3. Period: =INDEX(Tbl,MATCH(VLOOKUP(Dt,InnerTbl,1),OFFSET(Inn erTbl,,,,1))+1,1) 4. Week =INDEX(Tbl,1,MATCH(Dt,OFFSET(Tbl,MATCH(VLOOKUP(Dt, InnerTbl,1),OFFSET(InnerTbl,,,,1),0),,1,))) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
Calculating hours per week spent by a resource | Excel Discussion (Misc queries) | |||
Calculating tasks using a six day work week | Excel Discussion (Misc queries) | |||
Calculating the first day of the week | New Users to Excel | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions |