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

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

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 -

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