CALCULATE DAYS PER MONTH BETWEEN 2 DATES

  • Thread starter Thread starter wildliferehabber
  • Start date Start date
W

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
 
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
Click to expand...


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
 
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)
Click to expand...

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
 
Back
Top