Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of dates falling in a date range | Excel Worksheet Functions | |||
how to count number of days between 2 dates? | Excel Discussion (Misc queries) | |||
Count dates falling in a certain range | Excel Worksheet Functions | |||
Count number of days between dates BUT IF null to current date | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) |