Formula to display Count of Days in given month, using list of Startand End Dates

[Dave K]

I have a large number of rows that contain a start date and end date.
I am attempting to generate a count of days within that date range
that are in a particular month, for example, Jan of 2011.

Any suggestions for a formula that could be used to display the
amounts shown in Column C would be helpful.

Layout is:

A B C
Start Date End Date Count of Days in Jan2011
1/10/11 4/7/11 21
2/6/11 3/1/11 0
12/20/10 2/15/11 31
Etc.

 

[DCG-jaeson]

Dave,

I think the best way is to create a table from Start Date to End Date
then use the Formula below each cell in the table =text(cell,"m") then
count all 1

Cheerz!
~jaeson

 

[Ron Rosenfeld]

I have a large number of rows that contain a start date and end date.
I am attempting to generate a count of days within that date range
that are in a particular month, for example, Jan of 2011.

Any suggestions for a formula that could be used to display the
amounts shown in Column C would be helpful.

Layout is:

A B C
Start Date End Date Count of Days in Jan2011
1/10/11 4/7/11 21
2/6/11 3/1/11 0
12/20/10 2/15/11 31
Etc.

To duplicate your results requires not counting the first date, otherwise your January count would be 22. Is the 21 a mistake? Or do you really want to omit the first day.

If the result for January should really be 22, then, with the first of the month in C1 (e.g. 1/1/2011, which you could custom format to show, for example Jan 2011)

C2: =SUMPRODUCT((ROW(INDIRECT(StartDate&":"&EndDate))>=C$1)*(ROW(INDIRECT(StartDate&":"&EndDate))<=EOMONTH(C$1,0)))

and fill down as far as required.

Note that if you have other dates in row 1; so long as they are the first of the required month, you can also fill across to see the number of days in other months.

If you really don't want to count the first day, then change the references to StartDate to StartDate+1.

If you get a #NAME! error, and you are using a version of excel prior to 2007, check HELP for the EOMONTH function for instructions on installing the Analysis Tool Pak.

If you are using a version prior to Excel 2007, and cannot install the ATP, then use this formula instead:

C2: =SUMPRODUCT((ROW(INDIRECT(StartDate&":"&EndDate))>=C$1)*(ROW(INDIRECT(StartDate&":"&EndDate))<=DATE(YEAR(C$1),MONTH(C$1)+1,0)))