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.

I manage to do easy if you calculate night from 30.6. to 1.7. as it's night in 7th month...

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.

Look post #6, it will give you 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