count number of dates falling in the next seven days
 

I have a worksheet with a list of arrival dates. I need to count how many of
these dates fall within the next seven days. This calculation would need to
be self-updating on a daily basis. Is this possible?

count number of dates falling in the next seven days
 

B1=TODAY()
B2=TODAY()+7

=SUMPRODUCT(($A$1:$A$100B1)*($A$1:$A$100=<B2))

=COUNTIF($A$1:$A$100,"=<"&B2)-COUNTIF($A$1:$A$100,""&B1)

adjust your ranges to suit


On 29 Kwi, 11:58, Louisa wrote:
I have a worksheet with a list of arrival dates. I need to count how many of
these dates fall within the next seven days. This calculation would need to
be self-updating on a daily basis. Is this possible?

count number of dates falling in the next seven days
 

Assuming you have your dates in ColA. Try the below formula. If tried today
this will give you the count of dates from 04/30/09 to 05/06/09 both
inclusive.. You can adjust to suit your requirement


=COUNTIF(A:A,"<" & TODAY()+8)-COUNTIF(A:A,"<" & TODAY()+1)

If this post helps click Yes
---------------
Jacob Skaria


"Louisa" wrote:

I have a worksheet with a list of arrival dates. I need to count how many of
these dates fall within the next seven days. This calculation would need to
be self-updating on a daily basis. Is this possible?

count number of dates falling in the next seven days
 

That's brilliant, thank you so much. I've just realised that i have an added
complication in that i need separate arrival date tallys for men and women. I
have entered gender in column B as M or F, is it possible to add this as a
condition in the formula below?

"Jacob Skaria" wrote:

Assuming you have your dates in ColA. Try the below formula. If tried today
this will give you the count of dates from 04/30/09 to 05/06/09 both
inclusive.. You can adjust to suit your requirement


=COUNTIF(A:A,"<" & TODAY()+8)-COUNTIF(A:A,"<" & TODAY()+1)

If this post helps click Yes
---------------
Jacob Skaria


"Louisa" wrote:

I have a worksheet with a list of arrival dates. I need to count how many of
these dates fall within the next seven days. This calculation would need to
be self-updating on a daily basis. Is this possible?

count number of dates falling in the next seven days
 

Hello Louisa,

Then I suggest to use SUMPRODUCT:

=SUMPRODUCT(--(A1:A999<TODAY()+8),--(A1:A999TODAY()),--(B1:B999="M"))

resp.

=SUMPRODUCT(--(A1:A999<TODAY()+8),--(A1:A999TODAY()),--(B1:B999="F"))

Regards,
Bernd

count number of dates falling in the next seven days
 

Try the below formula. Change the M and F as required..

=SUMPRODUCT(--(A$1:A$100TODAY()),--(A$1:A$100<TODAY()+8),--(B$1:B$100="M"))


If this post helps click Yes
---------------
Jacob Skaria


"Louisa" wrote:

That's brilliant, thank you so much. I've just realised that i have an added
complication in that i need separate arrival date tallys for men and women. I
have entered gender in column B as M or F, is it possible to add this as a
condition in the formula below?

"Jacob Skaria" wrote:

Assuming you have your dates in ColA. Try the below formula. If tried today
this will give you the count of dates from 04/30/09 to 05/06/09 both
inclusive.. You can adjust to suit your requirement


=COUNTIF(A:A,"<" & TODAY()+8)-COUNTIF(A:A,"<" & TODAY()+1)

If this post helps click Yes
---------------
Jacob Skaria


"Louisa" wrote:

I have a worksheet with a list of arrival dates. I need to count how many of
these dates fall within the next seven days. This calculation would need to
be self-updating on a daily basis. Is this possible?