calculating number of days belonging to each month within a given week

[kate zareba]

Hello,
I want to calculate number of days belonging to each month within a given week, e.g. if i have
week no 1 29Dec08 until 4Jan09
week no 2 5Jan09 until 11Jan09
week no 3 12Jan09 until 18Jan09
week no 4 19Jan09 until 25Jan09
week no 5 26Jan09 until 1Feb09
I would like to calculate the split of days belonging to December, January,February etc; in this case it would be
week no 1 - 4 in Dec and 3 in Jan
week no 2 - 7 in Jan
week no 3 - 7 in Jan
week no 4 - 7 in Jan
week no 5 - 6 in Jan & 1 in Feb
I've tried to use eomonth formula but even with Analysis Tool Pack it doesn't work.
thank you.

EggHeadCafe - Software Developer Portal of Choice
JavaScript DatePicker
http://www.eggheadcafe.com/tutorial...-a679-9fe65c3493db/javascript-datepicker.aspx

 

[Ron Rosenfeld]

Hello,
I want to calculate number of days belonging to each month within a given week, e.g. if i have
week no 1 29Dec08 until 4Jan09
week no 2 5Jan09 until 11Jan09
week no 3 12Jan09 until 18Jan09
week no 4 19Jan09 until 25Jan09
week no 5 26Jan09 until 1Feb09
I would like to calculate the split of days belonging to December, January,February etc; in this case it would be
week no 1 - 4 in Dec and 3 in Jan
week no 2 - 7 in Jan
week no 3 - 7 in Jan
week no 4 - 7 in Jan
week no 5 - 6 in Jan & 1 in Feb
I've tried to use eomonth formula but even with Analysis Tool Pack it doesn't work.
thank you.

EggHeadCafe - Software Developer Portal of Choice
JavaScript DatePicker
http://www.eggheadcafe.com/tutorial...-a679-9fe65c3493db/javascript-datepicker.aspx

The specifics depend on you have your data set up, and how you want to display
the results. But, in general,

with a Starting Date in A2
Ending Date in B2

The number of days in that range that are in the same month as the starting
date: =SUMPRODUCT(--(MONTH(ROW(INDIRECT(A2&":"&B2)))=MONTH(A2)))

The number of days in that range that are in the same month as the ending date:
=SUMPRODUCT(--(MONTH(ROW(INDIRECT(A2&":"&B2)))=MONTH(B2)))
--ron

 

[JLatham]

If there is to be a split across 2 months, then the day of the month in the
ending date will be 1, 2, 3, 4, 5 or 6.

Assuming your first date is in cell A2 and the second date is in B2, this
formula will give you the number of days in the 1st month
=IF(DAY(B2)<7,7-DAY(B2),7)
and then (if that formula is in C2) you can use this to get the number of
days in the second month:
=7-C2