i have a column which shows the date that a task is complete, what i want is
a formula to show how many times the task has beem completed in a week. for
example if the column had the date 12/6/6 three times and the date 15/6/6,
seven times then between 12/6/6 & 18/6/6 the task has been completed ten
times.

is this possible, any ideas?

thanks

Assuming your dates are in cells A1 to A1000:

=SUMPRODUCT((A1:A1000>=DATE(2006,6,6))*(A1:A1000<=DATE(2006,6,18)))

Regards

Trevor


"hoyt" <(E-Mail Removed)> wrote in message
news:AE9B662A-3E87-4A91-AB12-(E-Mail Removed)...
>i have a column which shows the date that a task is complete, what i want
>is
> a formula to show how many times the task has beem completed in a week.
> for
> example if the column had the date 12/6/6 three times and the date 15/6/6,
> seven times then between 12/6/6 & 18/6/6 the task has been completed ten
> times.
>
> is this possible, any ideas?
>
> thanks

thanks Trevor
this works but it doesnt quite do what i need,assuming the dates are in
A1:A1000, i also have in Column B the week commencing dates from the start of
the contract which in this case is 5/6/06 then 12/6/06 then 19/6/06 and so
on, can your formula be made to work so as it will calculate how many times
the dates in A1:A1000 occur between the week commencing dates in Column B?

Sorry to be a pain

"Trevor Shuttleworth" wrote:

> Assuming your dates are in cells A1 to A1000:
>
> =SUMPRODUCT((A1:A1000>=DATE(2006,6,6))*(A1:A1000<=DATE(2006,6,18)))
>
> Regards
>
> Trevor
>
>
> "hoyt" <(E-Mail Removed)> wrote in message
> news:AE9B662A-3E87-4A91-AB12-(E-Mail Removed)...
> >i have a column which shows the date that a task is complete, what i want
> >is
> > a formula to show how many times the task has beem completed in a week.
> > for
> > example if the column had the date 12/6/6 three times and the date 15/6/6,
> > seven times then between 12/6/6 & 18/6/6 the task has been completed ten
> > times.
> >
> > is this possible, any ideas?
> >
> > thanks
>
>
>

thanks for the help Trevor i think ive done it the way i wanted by altering
your formula a little to:
=SUMPRODUCT((C4:C254>=VALUE(B7))*(C4:C254<=VALUE(B8)))

"Trevor Shuttleworth" wrote:

> Assuming your dates are in cells A1 to A1000:
>
> =SUMPRODUCT((A1:A1000>=DATE(2006,6,6))*(A1:A1000<=DATE(2006,6,18)))
>
> Regards
>
> Trevor
>
>
> "hoyt" <(E-Mail Removed)> wrote in message
> news:AE9B662A-3E87-4A91-AB12-(E-Mail Removed)...
> >i have a column which shows the date that a task is complete, what i want
> >is
> > a formula to show how many times the task has beem completed in a week.
> > for
> > example if the column had the date 12/6/6 three times and the date 15/6/6,
> > seven times then between 12/6/6 & 18/6/6 the task has been completed ten
> > times.
> >
> > is this possible, any ideas?
> >
> > thanks
>
>
>

Or, you could get away with:

=SUMPRODUCT((C4:C254>=B7)*(C4:C254<=B8))

Regards

Trevor


"hoyt" <(E-Mail Removed)> wrote in message
news:AC1A4E76-F6CE-4B7D-ACA2-(E-Mail Removed)...
> thanks for the help Trevor i think ive done it the way i wanted by
> altering
> your formula a little to:
> =SUMPRODUCT((C4:C254>=VALUE(B7))*(C4:C254<=VALUE(B8)))
>
> "Trevor Shuttleworth" wrote:
>
>> Assuming your dates are in cells A1 to A1000:
>>
>> =SUMPRODUCT((A1:A1000>=DATE(2006,6,6))*(A1:A1000<=DATE(2006,6,18)))
>>
>> Regards
>>
>> Trevor
>>
>>
>> "hoyt" <(E-Mail Removed)> wrote in message
>> news:AE9B662A-3E87-4A91-AB12-(E-Mail Removed)...
>> >i have a column which shows the date that a task is complete, what i
>> >want
>> >is
>> > a formula to show how many times the task has beem completed in a week.
>> > for
>> > example if the column had the date 12/6/6 three times and the date
>> > 15/6/6,
>> > seven times then between 12/6/6 & 18/6/6 the task has been completed
>> > ten
>> > times.
>> >
>> > is this possible, any ideas?
>> >
>> > thanks
>>
>>
>>

thanks Trevor


"Trevor Shuttleworth" wrote:

> Or, you could get away with:
>
> =SUMPRODUCT((C4:C254>=B7)*(C4:C254<=B8))
>
> Regards
>
> Trevor
>
>
> "hoyt" <(E-Mail Removed)> wrote in message
> news:AC1A4E76-F6CE-4B7D-ACA2-(E-Mail Removed)...
> > thanks for the help Trevor i think ive done it the way i wanted by
> > altering
> > your formula a little to:
> > =SUMPRODUCT((C4:C254>=VALUE(B7))*(C4:C254<=VALUE(B8)))
> >
> > "Trevor Shuttleworth" wrote:
> >
> >> Assuming your dates are in cells A1 to A1000:
> >>
> >> =SUMPRODUCT((A1:A1000>=DATE(2006,6,6))*(A1:A1000<=DATE(2006,6,18)))
> >>
> >> Regards
> >>
> >> Trevor
> >>
> >>
> >> "hoyt" <(E-Mail Removed)> wrote in message
> >> news:AE9B662A-3E87-4A91-AB12-(E-Mail Removed)...
> >> >i have a column which shows the date that a task is complete, what i
> >> >want
> >> >is
> >> > a formula to show how many times the task has beem completed in a week.
> >> > for
> >> > example if the column had the date 12/6/6 three times and the date
> >> > 15/6/6,
> >> > seven times then between 12/6/6 & 18/6/6 the task has been completed
> >> > ten
> >> > times.
> >> >
> >> > is this possible, any ideas?
> >> >
> >> > thanks
> >>
> >>
> >>
>
>
>