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

--
Neil_Pattiso
-----------------------------------------------------------------------
Neil_Pattison's Profile: http://www.excelforum.com/member.php...fo&userid=2769
View this thread: http://www.excelforum.com/showthread.php?threadid=54097

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" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> 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
>