Determine monthly volume from weekly figures where weeks cross over at month end

Good morning

I need to determine a monthly total on a worksheet where the figures are provided weekly. The challenfge is that some weeks do not start or end at the beginning / end of the month, so only portions form those weeks would be counted into each month.

For the purposes of this sheet, all days are assumed equal (ie the weekly total / 7 = daily figure). There is no weighting for weekends etc.

Please see attached example.Monthly total Sum.xlsx

I've deleted your column G, so that all your dates are contiguous, and so the first full week of a month also has the part-total from the previous week added in, and the final week of a month shows the total just for that month. As the formula looks at both the previous week's commencing date and the date for the following week, you also need to include the date for the previous week at the start and the following week at the end of the date range, with zero calls. I've coloured the formula cell blue in the attached file to illustrate this, where this formula is in C3 and copied across to T3:

Formula:

=IF(MONTH(C1)=MONTH(D1),C2+IF(MONTH(C1)=MONTH(B1),0,(DAY(C1)-1)/7*B2),(DATE(YEAR(C1),MONTH(C1)+1,0)-C1+1)/7*C2)

Then, with a date of 1/10/2012 in D6, this formula in E6 gives the total for that month:

Formula:

=SUMPRODUCT(--(TEXT($C1:$T1,"yymm")=TEXT(D6,"yymm")),$C3:$T3)

and I've copied both cells across with a gap of a few cells, and then changed the dates in the first of those cells (formatted to show the month name only).

Hope this helps.

Pete