Dates and number of days within a period

[Joe Kotroczo]

Hello,

I have the following problem:

I have 2 columns, 1 containing a start date and 1 containing an end date.
The dates, both start and end, can be either in September or in October.

What I need is 2 more columns, 1 with the number of days between start and
end which are in September and 1 with the number of days between start and
end which are in October.

I thought I could do something like
=IF(AND(MONTH(A1)=9,MONTH(B1)=9,DATEDIF(A1,B1,D) in the 1st column and
something similar with MONTH()=10 in the second, but this doesn't seem to
work, and I don't know what to do if the start date and end date are not in
the same month.

Has anybody got an idea?

Thanks,
Joe

 

[JE McGimpsey]

Hello,

I have the following problem:

I have 2 columns, 1 containing a start date and 1 containing an end date.
The dates, both start and end, can be either in September or in October.

What I need is 2 more columns, 1 with the number of days between start and
end which are in September and 1 with the number of days between start and
end which are in October.

I thought I could do something like
=IF(AND(MONTH(A1)=9,MONTH(B1)=9,DATEDIF(A1,B1,D) in the 1st column and
something similar with MONTH()=10 in the second, but this doesn't seem to
work, and I don't know what to do if the start date and end date are not in
the same month.

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.)