Need a formula to count no of days within a rage of days

[ahamed]

Hi

I have a range of dates which falls into different months. I want a formula to count no of such dates fall (within the range) for given month.

Pls help me on this.. Thank you in advance

 

[Aladin Akyurek]

Hi

I have a range of dates which falls into different months. I want a formula to count no of such dates fall (within the range) for given month.

Pls help me on this.. Thank you in advance

E1: 1-Jul-12

Try one of...

=SUMPRODUCT((A2:A100-DAY(A2:A100)+1=E1)+0)

=COUNTIF(A2:A100,">="&E1)-COUNTIF(A2:A100,">"&EOMONTH(E1,0))

These count dates which fall in the month of July in 2012.

 

[ahamed]

Thank you & it seems working ...

I need a small clarification, in my experience I have never come a cross the use of "&" in the count if formula

COUNTIF(A2:A100,">="&E1)-COUNTIF(A2:A100,">"&EOMONTH(E1,0))


Could you pls be kind enough to explain me the reason behind the use of "&" ?


Thank you in advance

 

[Aladin Akyurek]

Thank you & it seems working ...

You are welcome.

I need a small clarification, in my experience I have never come a cross the use of "&" in the count if formula

COUNTIF(A2:A100,">="&E1)-COUNTIF(A2:A100,">"&EOMONTH(E1,0))


Could you pls be kind enough to explain me the reason behind the use of "&" ?


Thank you in advance

The syntax of COUNTIF (and kindred functions) is:

COUNTIF(Range,"Operator"&CellOrCalculation)

& glues the contents of Cell or the result of Calculation to "Operator". Examples:

A1: 5

=COUNTIF(A2:A5,">"&A1)

==>

=COUNTIF(A2:A5,">5")

which gets evaluated, yielding a count of entries in A2:A5 greater than 5 ( > is the operaor ).

=COUNTIF(A2:A5,">="&MEDIAN(A2:A5))

would expand to the median value concatenated with ">=" and then evaluated to yield a count of values greater than or equal to the median value.

 

[ahamed]

Yes, I got now.

Thank you very much for taking time to explain me in this..

 

[Aladin Akyurek]

Yes, I got now.

Thank you very much for taking time to explain me in this..

You are welcome. Thanks for providing feedback.

 

[hibberts]

You are welcome. Thanks for providing feedback.

I have a similar problem... I have a start date and an end date and I need to know how many days from this range fall within certain months.

example:

1/15/16 2/9/16 How many days in range are in January? How many days are in February? etc.

Any help is greatly appreciated!!!

 

[Aladin Akyurek]

I have a similar problem... I have a start date and an end date and I need to know how many days from this range fall within certain months.

example:

1/15/16 2/9/16 How many days in range are in January? How many days are in February? etc.

Any help is greatly appreciated!!!

Care to post a small sample (not an image/picture or an outside link) along with the results that you want to obtain?