Log In
Register
Home
December 8, 2016
Offline
I have a buffer in Cell E1
I need the formula in Col F & G to select F1 where date is the 7th as well as the second last working day, However if the 7th is a weekend or public holiday, then amend formula where this is the first workday prior to the 7th. If the second last day is a weekend or public holiday, then amend formula where this is the first workday prior to the second last day in the month
i have attached sample data
Your assistance is most appreciated
I have also posted on Formula to increase buffer on certain weekdays (excelforum.com)
See Answer
July 16, 2010
Offline
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
December 8, 2016
Offline
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
July 16, 2010
Offline
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
December 8, 2016
Offline
Hi Mynda
thanks for the reply. the Closing Balance should be Opening Balance + Deposits - O/s Cheques - (to) frm Savings
Regards
Howard
July 16, 2010
Offline
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
Answers Post
1 Guest(s)
