Convert from Weekly to Monthly accurate to date

[z3162238]

Dear Expert

I have a summary of forecast which is grouped into weekly data.

I would like to create a summary table that show monthly data, however i need to be as accurate as to 1/4/2016 - 30/4/16. For the partial week forecast i will need to have it calculate using pro rata basis.

Is there anyway of doing this? I have tried offset, index and match with no luck.

Thank you so much for your help.

Cecilia

[jason.b75]

Try this one in F24.

=SUMPRODUCT(($A$7:$A$15=($A24&F$23))*(
(($G$1:$AH$1 >=DATEVALUE(1&F$22&2016))*($G$1:$AH$1< =(EOMONTH(DATEVALUE(1&F$22&2016),0)-6))*$G$7:$AH$15)+
(($G$1:$AH$1 >=(DATEVALUE(1&F$22&2016)-6))*($G$1:$AH$1< DATEVALUE(1&F$22&2016))*(($G$7:$AH$15/7)*(DAY($G$1:$AH$1+7)-1)))+
(($G$1:$AH$1 >=(EOMONTH(DATEVALUE(1&F$22&2016),0)-6))*($G$1:$AH$1<=EOMONTH(DATEVALUE(1&F$22&2016),0))*(($G$7:$AH$15/7)*(DAY(EOMONTH(DATEVALUE(1&F$22&2016),0))-DAY($G$1:$AH$1)+1)))))

Please note that when you copy the formula to excel, you will receive notification that the formula contains an error and excel will ask if you want to accept the automatic correction. Click 'Yes' when this happens. The error is caused by extra spaces in the formula that I had to add in because the forum software was misreading it as html code.

[z3162238]

Hi Jason,

What if I want to have the start date as a date of my choice such as 15/4/2016 or 9/4/2016. Is there a way to do so in this formula?

Also can you please teach me how to understand (DAY(EOMONTH(DATEVALUE(1&F$22&2016),0))-DAY($G$1:$AH$1)+1) of the formula what does it do to the calculation.

Thank you so much!!

[z3162238]

Hi Jason,

What if I want to have the start date as a date of my choice such as 15/4/2016 or 9/4/2016. Is there a way to do so in this formula?

Also when I change the date to 1/7/2016 its result is incorrect as it has calculated 39.5 instead of 31.06. Can this be fixed?

Also can you please teach me how to understand (DAY(EOMONTH(DATEVALUE(1&F$22&2016),0))-DAY($G$1:$AH$1)+1) of the formula what does it do to the calculation.

Thank you so much!!

[jason.b75]

If you wanted to use specific dates then the formula would need to be re-written, in the current form it is specifically set up for calendar months. I don't think it will be possible to do calendar months and specific dates with one formula, or at least not one that is practical to use.

Could you upload a sample file showing the incorrect result? I can't see how you got that figure from the original sample, so I have to assume you're working with different data now.

Bear with me on the explanation that you asked for, it essentially calculates the number of days to include in a week that is split over 2 months, but I need to go over the formula to remind myself how it works, although, if the formula is returning incorrect results then there is a chance that this might be the part of it that needs correcting.

[z3162238]

If you wanted to use specific dates then the formula would need to be re-written, in the current form it is specifically set up for calendar months. I don't think it will be possible to do calendar months and specific dates with one formula, or at least not one that is practical to use.

Could you upload a sample file showing the incorrect result? I can't see how you got that figure from the original sample, so I have to assume you're working with different data now.

Bear with me on the explanation that you asked for, it essentially calculates the number of days to include in a week that is split over 2 months, but I need to go over the formula to remind myself how it works, although, if the formula is returning incorrect results then there is a chance that this might be the part of it that needs correcting.

Thank you so much Jason!
I'm using the same set of data but the formula on top I've changed it to date plus 3 as our week start on Monday.

[jason.b75]

I still need a copy of the file with the changes so that I can check the formula, I've changed the dates as you say above but still not getting 39.5 for July, (for 1A I get 32.084).

Also, looking at your date formula in G1:AH1, it appears that there could be a partial week at the start of the year, which will cause the formula to fail, it is set up on the basis that each column covers a period of 7 days, any more or less will cause incorrect evaluation.

[z3162238]

I still need a copy of the file with the changes so that I can check the formula, I've changed the dates as you say above but still not getting 39.5 for July, (for 1A I get 32.084).

Also, looking at your date formula in G1:AH1, it appears that there could be a partial week at the start of the year, which will cause the formula to fail, it is set up on the basis that each column covers a period of 7 days, any more or less will cause incorrect evaluation.

I have changed the first formula that stated the weekday that start the week and it gives me the incorrect result in July. All other months are perfectly fine thou.

PS the problem is with suming 1B for July

Thank you so much Jason.

Cecilia

[jason.b75]

Results look correct to me, what were you expecting for July?

My guess would be 39.77, which would be incorrect. You have 2 rows of data for 1B and 39.77 is the total for the first row only.