Thread: Prorate formula for weekly to monthly data

I often have data sets that run on a weekly basis, but I need to prorate the weekly data into an estimated monthly total. I will take the number of days that fall in each month from the first week of the month and apply X number of days of that week's data into the current month, with the remaining portion going to the previous month. For example, if I have a weekly number of 700 for a week that ended October 4, 4 days of that value, or 400would be applied to October, while the other 3 days that were in September will add 300 to the September total. All of the full weeks that are fall in the month are simply added.

Below is a sample of data I use. The first week would have 479 applied to June (839/7*4 - for 4 days in June), while the last week would have 611 added to June (855/7*5), with June being a total of 3607.

6/4/10 839
6/11/10 839
6/18/10 846
6/25/10 832
7/2/10 855

Is there a ongoing formula I could use to copy down for it to always calculate the splits across the first weeks of each month and provide a running monthly total as the formula is copied down?

Thanks

Hi,

One way with your table in A1:B5

In say C1 and copied down

=B1/7*IF(DAY(A1)<7,DAY(A1),7)+IF(MONTH(A2)<>MONTH(A1),B2/7*(DAY(EOMONTH(A1,0))-DAY(A1)),0)

HTH