Converting weekly revenue into months - need to split data if week covers more than one month

[Jamie W]

Hi all,

Firstly - love the forum, I've had a good look through previous threads and there is lots of great info on here.

I have had a look for similar problems to mine, but couldn't find anything applicable. Apologies if this question has come up and been answered before.

I am using a legacy system that spits out weekly revenue by project. The week columns are labeled by the date for the first Monday of that week. Each week, we manually add on monthly columns and then add the appropriate weekly totals, in order to get monthly revenue totals by project.

If a week contains revenue for more than one month (e.g. Week commencing 29 June 2015contains 3 working days from July), then we have to manually split the data for that week and proportion it to each month (e.g. divide by 5, multiply by 2 for the June column, and 3 for the July column)

I would like to achieve the above using a formula but I cannot for the life of me figure the logic out!

Does anyone have any suggestions for how to tackle this?

The report will always spit out 52 weeks in advance of the week that it is run, so the spreadsheet needs to be able to factor in that weekly data will drop off with time, and new weeks/months will appear in subsequent reports.

I have uploaded a sample file showing how the data is structured.

Apologies if the above explanation is not clear - I am more than happy to clarifying anything further!

Many thanks in advance for assistance.

 

[Nebu]

Hi:

Please find the attached. I had to use helper columns, hope this is what you are looking for.

Thanks

 

[Jamie W]

Hi:

Please find the attached. I had to use helper columns, hope this is what you are looking for.

Thanks

Hi Nebu, thanks so much for this.

This is almost what I need but there's a couple of issues that I am trying to resolve.

Firstly, I need to divide the revenue by just 5 working days per week, not the full 7 days. e.g. in cell F17 it should be (2895/5) not (2895/7). No revenue should be attributed to weekend dates.

Secondly, the result in F17 should be for 2 days revenue - not just 1 day (29 and 30 June are both working days, so it should be (2895/5)*2...does that make sense?

Thanks again - this is really helpful so far.

 

[Nebu]

Hi:

I have made necessary changes.

Thanks