Excel 2007 : Counting date in specific month by month

In A3 put

=IF(MONTH($A2)=MONTH(C$1),DATE(YEAR($A2),1+MONTH($A2),0)-$A2+1,0)

Sadly - that's as far as I can go at the moment. This calcs the days from the start to the end of that current month.

This calc to the end of the month =DATE(YEAR(A2),1+MONTH(A2),0)+1 and needs incorporating

Have to go offline now but hopefully someone can follow this up.

Very good - a new formula for me to learn - MEDIAN.

So, if I understand correctly - when used together MEDIAN and DATE

Please Login or Register  to view this content.

with DATE +1 it is looking for the two months of the year surrounding the month in C2 (6 - June) and the MEDIAN instruction finds the midway point i.e. in this case the date where the month changes.

DATE + 1 will look for last day of current month...

Therefore DATE(2012, 7, 0) is 30.06.2012 (that's why it's +1)

MEDIAN return middle value of 3 numbers so it will substract 30.06. - 28.06 = 2
And look that on second column onward you also have SUM function (or you could leave one empty column and use them in all formulas).

Edit: Stupid me... Answering your question I figure out that I need just to use DATE(2012, 7, 1) (instead of 0) to get desired result.

If you put the 1st of each month in C2 across then you can use this formula in C3 copied across and down

=MAX(0,MIN(EOMONTH(C$2,0),$B3-1)+1-MAX(C$2,$A3))

see attached