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

ahamed

Board Regular
Joined
Jan 11, 2011
Messages
114
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:):)
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Board Regular
Joined
Jan 11, 2011
Messages
114
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Board Regular
Joined
Jan 11, 2011
Messages
114

ADVERTISEMENT

Yes, I got now.

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

hibberts

New Member
Joined
Feb 14, 2006
Messages
16
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top