counting occasions dates occur between 2 dates
 

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

counting occasions dates occur between 2 dates
 

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" wrote in message
...
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

counting occasions dates occur between 2 dates
 

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" wrote in message
...
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

counting occasions dates occur between 2 dates
 

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(B 8)))

"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" wrote in message
...
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

counting occasions dates occur between 2 dates
 

Or, you could get away with:

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

Regards

Trevor


"hoyt" wrote in message
...
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(B 8)))

"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" wrote in message
...
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

counting occasions dates occur between 2 dates
 

thanks Trevor


"Trevor Shuttleworth" wrote:

Or, you could get away with:

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

Regards

Trevor


"hoyt" wrote in message
...
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(B 8)))

"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" wrote in message
...
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