Hi Howard,

Your question isn't clear to me, sorry. You mention a buffer in cell E1, but then refer to F1, which is an empty cell. Is this a mistake? Your formula in columns  F and G do a lot more than what you describe above, so I'm not sure what exactly you want. 

Your examples in the file do not illustrate your desired result as far as I can see and your description appears incomplete e.g.:

"However if the 7th is a weekend or public holiday, then amend formula where this is the first workday prior to the 7th." Amend the formula to what?

Perhaps you can use the WORKDAYS,INTL function to identify whether a date is a weekend or public holiday. However, you will need to add proper dates in the file, which are required to determine if a date is a public holiday (which also aren't listed).

Mynda

Hi Mynda

Sorry for not being clear in post # 1

I have a buffer in Cell E1 as well as an increased buffer in Cell F1

I have a formula in Col F in row F10 onwards. where I need to compute how much to transfer to savings, taking into account that the closing balance must not go below the buffer in E1

i need to amend my formula so that E1 changes to F1 on the 6th (if the 6th falls on a weekend or public holiday then the first working day prior to this) as well as one working day before the last day of the month.

it would be appreciated if you could amend my formula taking the above into account

Hi Howard,

Thanks for clarifying. Another question:

The closing balance doesn't look right to me because it doesn't reconcile to any of the numbers in the row, with the exception of the first value which adds the CHQS to the Opening Balance. Also, the formula in this column references an empty cell in column BY and adds the day number from column B, which seems odd. Please confirm the closing balance should be:

= Opening Balance + O/S CHQS - To/From Savings

Mynda

Hi Mynda

thanks for the reply. the Closing Balance should be Opening Balance + Deposits - O/s Cheques - (to) frm Savings

Regards

Howard

Hi Howard,

With complex conditions like this it's easier to add some helper columns when working it out. In the attached file I've added some tables to columns O and P to identify the months where the 6th falls on a weekend or holiday and what the last working day before the end of the month -1 is. I then reference these tables to identify the minimum balance required on each day. 

Once we know the minimum balance required, the To/From Savings amount is easy to calculate.

If you want, you can consolidate the formulas into a single cell for each date, but I don't recommend it as there's nothing really to gain.

Note: I've changed the values in column A to proper dates, as you'll see if you select any of them and look in the formula bar. This is essential when working with dates like this. 

Hope that helps.

Mynda

Hi Mynda

Thanks for all your effort in amending my cash flow

This is brilliant and is of great help

Regards

Howard