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

=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
"Michelle" wrote:

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

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)

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
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

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

M


"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
Thanks - works a treat - I love the help I get in these groups

M


"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
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