Taking weekly data and converting to monthly

Hi all,

I have a data set that contains a number of week ends i.e. week ending 03/09/23, 10/09/23 etc. and I need to summarise this into a monthly summary. Where the week ending is over two months i.e. 03/09/23 I want it to take the total and divide by 7 and multiply it by the number of days in the month i.e. for 03/09/23 I would want 3 days worth in Sept and 4 days in Aug. Then on top of this I need it to look up categories and bring it back based on those as well as the dates.

Example sheet attached.

The weekly forecast has a helper row in it (Row 10) which identifies the number of days in the month (this may not be needed). I need the formula in the Summary tab cells D13:O26 and D31:O58.

I have the following formula used to do this based on only one sum range and no additional criteria but I don't know how to amend it to do what I need. (see Sheet 1 tab in the file)

=SUMPRODUCT((DATE(YEAR($G$9:$G$18),MONTH($G$9:$G$18),1)=DATE(YEAR(O$8),MONTH(O$8),1))*$I$9:$I$18/7*($H$9:$H$18))+SUMPRODUCT((DATE(YEAR($G$9:$G$18),MONTH($G$9:$G$18),1)=DATE(YEAR(O$8),MONTH(O$8)+1,1))*(7-$I$9:$I$18)/7*($H$9:$H$18))

Thanks in advance.
James

The other criteria is in Col B.

Try in D13 and copy across and down:

Formula:

Please Login or Register  to view this content.

Good luck!

Hi, this doesn't quite work. It does everything except take the days after 24th Sept to 30th Sept which are in the 01/10/23 column. These need to be divided by 7 and multiplied by the number of days in the month (for the 1/10/23 it would be 6 as 6 are in sept and 1 are in October.

I have updated the file with the formula given above (cell E13) which doesn't quite work. I've added another formula (D13) that incorporates the formula in cell E13 with another formula that attempts
to apportion any amounts in the week endings that go over two months but this doesn't work...I don't know why? I have added notes onto the file which may help explain.

Can someone either help amend the formula in cell D13 to work or provide another formula that works please?

Thanks in advance.
James

You have the actual daily figures in the Bank Feed sheet, why do you need to take the Weekly Forecast figures and do apportionment for weeks spanning over 2 months?

Modifying the Weekly Forecast formula for Summary:
=SUMPRODUCT(('Bank Feed'!$C$7:$PP$42)*('Bank Feed'!$B$7:$B$42=$B13)*('Bank Feed'!$C$2:$PP$2>=D$8)*('Bank Feed'!$C$2:$PP$2<=EOMONTH(D$8,0)))

Hi JeteMc/Josephteh,

Thanks for your responses. The reason I want to take it from the weekly forecast sheet and not the bank feed is because I will be forecasting in the weekly forecast sheet. The weekly forecast sheet will be made up of actuals from the bank feed and then forecasts from the forecast sheet (which is also weekly). I don't want to enter the forecast in the bank feed or have the forecast displayed daily.

At the moment I have used this formula to pull the data from the weekly into the monthly... which works but it doesn't reference the criteria in col B. I have to use specific cell references for each row. Which is fine as long as they don't change.

=SUMPRODUCT((DATE(YEAR('Weekly Forecast'!$D$8:$BD$8),MONTH('Weekly Forecast'!$D$8:$BD$8),1)=DATE(YEAR(Summary!D$8),MONTH(Summary!D$8)+1,1))*(7-'Weekly Forecast'!$D$10:$BD$10)/7*'Weekly Forecast'!$D29:$BD29)+
SUMPRODUCT((DATE(YEAR('Weekly Forecast'!$D$8:$BD$8),MONTH('Weekly Forecast'!$D$8:$BD$8),1)=DATE(YEAR(Summary!D$8),MONTH(Summary!D$8),1))*'Weekly Forecast'!$D$10:$BD$10/7*'Weekly Forecast'!$D29:$BD29)

James

That works. Thank you... much appreciated.