Configuring Dates

[Neil_Pattison]

I have a problem where I need to be able to calculate when a date fall
into a certain week.

For example I have a spreadsheet that covers the certain projects tha
we are carrying out on which I enter start dates and end dates for th
work, and I need to be able to calculate how many jobs were bein
carried out on a specific week.

I have set up a simple list on a seperate worksheet showing a colum
each for the week number, start date and end date i.e. Week One star
date is 2nd Jan 2006 and end date is 8th Jan 2006; Week Two start dat
is 9th jan 2006 and end date is 15 Jan 2006

I want to be able to choose a week number and be shown how many job
were being carried out that week i.e. Week 18 would show any jobs tha
were being carried out between 1st May 2006 and 7th May 2006

Any help would be greatly appreciate

 

[Bob Phillips]

Here is an example

=SUMPRODUCT(--(ISNUMBER(A2:A20)),--(INT((A2:A20-"2006-01-02")/7)+1<=week_num
),--(ISNUMBER(B2:B20)),--(INT((B2:B20-"2006-01-02")/7)+1>=week_num))

week_num is a named cell where you would store say 18, and the date in the
formula is the day 1 of week 1.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Neil_Pattison" <[email protected]>
wrote in message
news:[email protected]...