Hi,

I have a list of Monthly direct debits, like:

Day Payee Amount
1 CoA £100
5 CoB £300
7 CoC £222
9 CoD £999

And so on.

I then have a list weeks of the year and the corresponding Monday date.

For each of those weeks I need to figure out the direct debits that will be coming out of the bank.

If a direct debit day falls on a weekend assume it comes out the friday before.

And ideas?

I tried to do lots of if statements but sure there must be more elegant way…

is “Day”, day number in year, day number in month, day number in week?

Day is day number in a month, so a direct debit on that list with a Day of 5 would come out of the bank on the 5th of every month.

But if the 5th was a weekend it would come out on the friday before the weekend…

Its bloody complicated, I tried converting to weeknumbers also which I thought might be a smart way.

OK.

I reckon you need to:

create a table with month names in row 1 (starting from C1), month numbers in row 2 (from C2), and DD id down left hand column A with DD monthday in the column next to it B.

Then formula in C3 and copied 12 colums right and X rows down is: =IF(OR(WEEKDAY(DATE(2011,C$2,$B3),3)=6,WEEKDAY(DATE(2011,C$2,$B3),3)=7),WEEKNUM(DATE(2011,C$2,$B3))-1,WEEKNUM(DATE(2011,C$2,$B3)))

Where X is the number of DDs you have

This gives you a table with the week number that a given DD id falls due, coming back one week if it would normally fall at the weekend.

You now need to create a second table of the same dimensions as your weeknumber grid you’ve made above (lets say 12 months wide and 40 DDs long). Populate this grid with the DD value that you have in the far left hand column (i.e. £200 for DD no. 3 that occurs on the 8th day of each month). copy this value all the way across the months.

Below this now put a list of weeknumbers, next to that list use a sumif that returns the sum of the DD values in the DD value grid if the Weeknumber values (from the upper grid) match the criteria weeknumber in your list.

You will lose the identifying DD data (this is because really your are doing three-dimensional analysis with a 2 dimensional tool – really you need MSAccess to do this kind of thing) but it will tell you how much will come out by DD in any given weeknumber for any number of DD’s.

Thanks, will give it a go!