Configuring Dates

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

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

I have set up a simple list on a seperate worksheet showing a column each for the week number, start date and end date i.e. Week One start date is 2nd Jan 2006 and end date is 8th Jan 2006; Week Two start date 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 jobs were being carried out that week i.e. Week 18 would show any jobs that were being carried out between 1st May 2006 and 7th May 2006

Any help would be greatly appreciated

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" <Neil_Pattison.27mxlb_1147337401.511@excelforum-nospam.com>
wrote in message
news:Neil_Pattison.27mxlb_1147337401.511@excelforum-nospam.com...
>
> I have a problem where I need to be able to calculate when a date falls
> into a certain week.
>
> For example I have a spreadsheet that covers the certain projects that
> we are carrying out on which I enter start dates and end dates for the
> work, and I need to be able to calculate how many jobs were being
> carried out on a specific week.
>
> I have set up a simple list on a seperate worksheet showing a column
> each for the week number, start date and end date i.e. Week One start
> date is 2nd Jan 2006 and end date is 8th Jan 2006; Week Two start date
> 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 jobs
> were being carried out that week i.e. Week 18 would show any jobs that
> were being carried out between 1st May 2006 and 7th May 2006
>
> Any help would be greatly appreciated
>
>
> --
> Neil_Pattison
> ------------------------------------------------------------------------
> Neil_Pattison's Profile:
http://www.excelforum.com/member.php...o&userid=27696
> View this thread: http://www.excelforum.com/showthread...hreadid=540970
>