Mac Forum / Applications / Excel / August 2007

First - dates are stored in XL as integer offsets from a base date
(e.g., 15 August 2007 is stored as 37847, since it's 37847 days after
1/1/1904, using the 1904 date system), so it's not necessary to use
DATEDIF() to calculate days - one can add/subtract the dates directly
(format the result as General or another date format if necessary).

Second, your question is slightly ambiguous - do you wish to count both
start and end dates as days? For instance, should a start date of 15
September and an end date of 16 September be counted as 1 day or 2? The
answer below assumes that both start and end dates should count (hence
the '+ 1' at the end of the formula).

To answer your question literally, hard-coding September and October of
2007, you could use something like:

C2:     =MAX(0, MIN(DATE(2007,10,0), B2) - A2 + 1)
D2:     =MAX(0, B2 - MAX(DATE(2007,10,1), A2) + 1)

If, instead, you wanted to make this more flexible, and put, say, a
start date in another cell (say, J1) so that column C represented the
number of days in the start date's month, and column D represented the
number of days in the month after that:

C2:     =MAX(0, MIN(DATE(YEAR($J$1),MONTH($J$1)+1,0), B2) - MAX(A2,
DATE(YEAR($J$1),MONTH($J$1),1)) + 1)

D2:     =MAX(0,MIN(B2, DATE(YEAR($J$1),MONTH($J$1)+2,0)) -
MAX(DATE(YEAR($J$1),MONTH($J$1)+1,1), A2) + 1)

(Note that XL considers day 0 of a month to be the last day of the
preceding month.)