Weeks to Months - Pro-rate

[kingpin99]

Hey Guys

Need some help on the file below
https://www.dropbox.com/s/kigw2gk2jx0l37x/WeekToMonths.xlsx?dl=0

I have data for sales in column A by each week.
The sales need to be pro-rated by month, when the week commencing over laps with another month.

e.g. Week commencing the 29/01/2018 ends on the 04/02/2018.
3 days of sales need to be in Jan
4 days of sales need to be in Feb

I don't mind if the sales for week 29/01/2018 falls into week 22/01/2018 for the 3 days and the 4 days can go into 05/03/2018 as long, as it falls in the correct month.
I don't want to add rows.

is there anyway this can be achieved?

Thanks in advance

 

[Rick Rothstein]

You will have to test it, but I am pretty sure this formula placed in cell F2 and copied down will do what you want...

=IF(C2=E2,A2+IF(C3=E3,0,A2*(EOMONTH(B3,0)-D2)/7)+IF(C1=E1,0,A1*DAY(D1)/7),"")