CALCULATE DAYS PER MONTH BETWEEN 2 DATES

[wildliferehabber]

I am trying to calculate days between a start date and end date for each
month. If start date and end date are same day, formula should return 1.
Columns A-AD are in use for other data. Below is an example of what I want to
see. Help, please?

Example:
A B ... N … R S T … AB AC
1 Start End JAN FEB MAR NOV DEC
2 1/2/09 1/2/09 1
3 1/16/09 3/27/09 16 28 27
4 2/3/09 3/11/09 26 11
5 11/13/09 12/13/09 18 13
6 1/12/09 12/1/09 20 28 31 30 1

 

[Lars-Åke Aspelin]

I am trying to calculate days between a start date and end date for each
month. If start date and end date are same day, formula should return 1.
Columns A-AD are in use for other data. Below is an example of what I want to
see. Help, please?

Example:
A B ... N … R S T … AB AC
1 Start End JAN FEB MAR NOV DEC
2 1/2/09 1/2/09 1
3 1/16/09 3/27/09 16 28 27
4 2/3/09 3/11/09 26 11
5 11/13/09 12/13/09 18 13
6 1/12/09 12/1/09 20 28 31 30 1

With the start date in cell A2 and the end date in cell B2, try the
following in cell C2:

=SUMPRODUCT(--(DATE(YEAR($A2),COLUMN(A1),
ROW(OFFSET($A1,,,DAY(DATE(YEAR($A2),COLUMN(B1),0)),)))>=$A2))
-SUMPRODUCT(--(DATE(YEAR($A2),COLUMN(A1),
ROW(OFFSET($A1,,,DAY(DATE(YEAR($A2),COLUMN(B1),0)),)))>$B2))

Copy this formula to the right thru cell N2.
Then copy cells C2:N2 down as far as you have data in columns A and B.

Note: This formula will only work if there is only one calendar year
involved, i.e. the start date and end date within the same year.

Hope this helps / Lars-Åke

 

[barry houdini]

If your header row contains dates, i.e. the 1st of each month (you ca
custom format these as mmm if you just want to display "Jan","Feb" etc.
then you can use this formula in C2 copied across and down

=MAX(0,MIN($B2,C$1+31-DAY(C$1+31))-MAX($A2,C$1)+1

 

[Lars-Åke Aspelin]

If your header row contains dates, i.e. the 1st of each month (you can
custom format these as mmm if you just want to display "Jan","Feb" etc.)
then you can use this formula in C2 copied across and down

=MAX(0,MIN($B2,C$1+31-DAY(C$1+31))-MAX($A2,C$1)+1)

This is nice. However, be aware the limitation that the start and end
dates must be within the calendar year that you have used for the
header row dates.
When you have a new year you will need a new header row.

Lars-Åke