Summing weekly values to fall into correct month

[KM]

Hi,
Could someone please help me with this .

My spreadsheet has row data in weekly buckets . I would like these weekly
amounts to be summed to form the corressponding monthly total . However ,
some weeks are split across 2 months.

For e.g. w/c 28/06/10 has 3 days that fall into June and 4 days that fall
into July.
1) How do I get excel to add up all the weeks in June, and for this
particular week, instead of taking the full weekly value into June ,
calculate the value/7 * 3 days ?
2) How do I get the following month of July to also add in the value/7*4
days from this week , as it falls into July ?

w/c 07/06/10 14/06/10 21/06/10 28/06/10 05/07/10
12/07/10
Month Jun Jun Jun Jun
Jul Jul
Days/week 7 7 7 3 + 4
7 7

I'm not familiar with macros, but have used a few worksheet formulas . Can't
seem to find any vlookup/IF formula that will take all these conditions into
account .

Any help would be much appreciated.
Kind Rgds
KM

 

[Herbert Seidenberg]

Excel 2007 PivotTable, PivotChart
Convert week number (US) into date range,
then group by quarter, month, day.
http://www.mediafire.com/file/jtg5z41zmdm/01_28_10.xlsx

 

[KM]

Hi Herbert,

Thanks very much , will give it a try .

Kind Rgds
Kirthana