Cashflow help - predicting bank payments out

[Spicegirl]

Hi

Different issue, but same cashflow...

I have a feeder spreadsheet into my cashflow that has an assortment of automatic bank payments (direct debits for UK readers) and when they are due and dates listed. At the moment the (inherited) formula is very basic i.e. if day of month = 26 then return the value for salaries etc etc. Obviously this has led to it returning values on weekend days and non-working days. I can work out how to make it return £0 if it's a non working day, but how do I make the next (or previous) working day report this figure? I suppose the simplest solution is to put a formula against the salary line and report the date of the next/previous working day and use that, but the data covers 39 weeks so that's 9 months of dates I would have to put in for every expense instead of a formula working it out.

Is there a way to do it so that I can shift values from the day they should come out (if it's a non-working day) without disrupting other values that are due out that day. For instance, if we were due to pay 2 types of taxes, one on 15th October and one on 17th, I would like the 17th to report both.

Thanks in advance

 

[Spicegirl]

Here's a sample file. As you can see in the sample file I have set dummy pension and HMRC tax amounts to go out on the 15th and 16th October which happen to be the weekends. I want these formulae to a) Report a zero if the date is a weekend or non-working day and b) report it on the next working day row instead (or potentially previous - bank payments will go for the next working day yet things like HMRC and salaries will go out on the previous, so it depends on what the payment is - I should be able to follow standard logic on this though).