How many days fall in each month?

[Michelle]

I have a date range (say... 12/5/2009 to 27/9/2009) with the two dates in
two cells, say... A1 and A2

and a table where each of my column headings is a month (they're actually
dates representing the 1st of each month, but formatted to just show the
month and year e.g.Sep 09 - although in tructh, I don't really care what
they are)

Anyway I want to have the number of days that fall in that month displayed
under each month name. i.e. if my date range starts on 12/5/2009, May 09
will have 20 below, June 09 will have 30 below, July 09 will have 31 below
etc. until the end of the range when (if my range finishes on 27/9/09) Sep
09 will have 27 below. All other months will have a blank or zero.

In my ideal world this would not be an array formula, because I might need
to delete or edit certain cells/columns, but if that's the only way then so
be it

I've been struggling with this for a while - and I haven't got very far at
all!

I'd be grateful for any ideas

Many, many thanks

M

 

[Patrick Molloy]

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

the zero'th day of month (n+1) is the last day of month n eg the 0 june is
teh

 

[T. Valko]

Try this:

1st of the month *dates* starting in B1:

Enter this formula in B2 and copy across as needed:

=MAX(0,MIN(B1+32-DAY(B1+32),$A2)-MAX(B1,$A1)+1)

 

[Michelle]

Thanks - works a treat - I love the help I get in these groups

M

 

[T. Valko]

You're welcome. Thanks for the feedback!