If Date-7 or Date+7 includes dates in another month, then...

Sorry, I couldn't think of a better title. I have a spreadsheet with payroll costs by department by week. I've developed a monthly report which requires manual manipulation of some data - specifically, if the payroll week goes between months, only a portion should be allocated to a specific month (e.g. if there's a weekly payroll ending 2/3/2011, you take the payroll cost for the week, divide it by 7 and multiply that number by 4 or 3 for the allocations to January and February, respectively) - but I'd like to know if it's possible to have Excel do all of this work automatically. I'd like to both reduce the potential for error and increase the speed in which these reports can be generated each month.

I've attached a spreadsheet showing how the data is organized and a demonstration of the problem.

Thank you.

Adam

try this method and let me know if it works for you..

If you make F33 into a true date, 1st of the relevant month, as per Cutter's example then you can use this formula for the total

=SUMPRODUCT(LOOKUP(ROW(INDIRECT(F33&":"&EOMONTH(F33,0)))+6,$4:$4,8:8))/7

Forget this message...my mistake

OK, you normally have to start from the inside out, i.e. in this formula

=SUMPRODUCT(LOOKUP(ROW(INDIRECT(F33&":"&EOMONTH(F33,0)))+6,$4:$4,8:8))/7

F33 is the start date of the month and EOMONTH(F3,0) generates the end date of the month so this part

ROW(INDIRECT(F33&":"&EOMONTH(F33,0)))

is a way to generate an array of dates including every day in the month.

Now with LOOKUP you can lookup a value in one row and return the corresponding value in another row, e.g.

=LOOKUP(A1,2:2,3:3)

In that version the values in row 1 need to be sorted ascending....and then A1 will match with the next lowest (or equal) value in row 2 and return the corresponding value from row 3...so if A1 is 5 and A2:C2 is 1, 4 and 7 then A1 matches with the next lowest value, 4 in B2 and returns the value from B3.

In your version the dates are in ascending order...but they are week end dates so you need to add 6 to the lookup dates, so now the LOOKUP is like this

=LOOKUP({array of dates}+6,$4:$4,8:8)

So, fortunately for me LOOKUP allows you to have an array or range as the LOOKUP value (unlike VLOOKUP, for instance) and the above therefore looks up all the dates (+6) in the date row and returns the values from the value row.

So when you lookup the first date it's the 7th of Jan (1st +6) so that matches with D4 (next lowest date) and the formula returns D8 value....but the next date (8th Jan) matches with E4 and E8 is returned....in fact E8 is returned 7 times, F8 7 times etc.....so for full weeks you get 7 values and a reduced number for weeks at the start of the month.

SUMPRODUCT then sums all the value (31 for January) and then we divide by 7, effectively giving you E8 only once but D8/7 etc.....

If you have the data in data worksheet then you can just change the formula to include the sheet name, i.e.

=SUMPRODUCT(LOOKUP(ROW(INDIRECT(F33&":"&EOMONTH(F33,0)))+6,Data!$4:$4,Data!8:8))/7

I'm assuming that F33 will be in the same sheet as the formula......