Predictive ACH Calandar sensitive to Banking Holidays and Weekend Days

[bullock2270]

			1=No, 0=Yes
Day	Date	Status	OPEN	BOL #	Invoice TTL	Draft Date	Add Days
Monday	1/1/18	N	1			1/11/18	0
Tuesday	1/2/18	Y	0			1/12/18	0
Wednesday	1/3/18	Y	0			1/13/18	1
Thursday	1/4/18	Y	0			1/14/18	1
Friday	1/5/18	Y	0			1/15/18	1
Saturday	1/6/18	N	1			1/16/18	0
Sunday	1/7/18	N	1			1/17/18	0
Monday	1/8/18	Y	0			1/18/18	0

<tbody>
</tbody>

I am attempting to build a spreadsheet that would predict ACH Date (10 Day Terms) but with respect to banking holidays and weekend days. For example: an invoice date of 1/3/18 would normally result in an ACH on 1/13/18; however, i will not draft until 1/16 as the bank is closed SAT,SUN & MON due to weekend and banking holiday back to back. I have applied a value of 1 if closed on a date and a 0 if open. Im trying to craft a formula that will use the calculated ACH date if adjacent cell =0, however, I also need the formula to continue to add a draft date for each consecutive day until it arrives at the next next day bank is open. Thanks for any assistance.

 

[Peter_SSs]

Welcome to the MrExcel board!

Have a look at the WORKDAY function.

 

[bullock2270]

Thanks for direction on identifying weekends and Banking Holidays. I have 2 colums: 1 with ACH drafts that will happen on a day the bank is open. The other column has the amount that is due to draft on a holiday. I need to find a function that will sum the cells with numbers >1 together and total in the next cell with a zero balance. For instance, I need a function that will group 1/13,1/14 &1/15 of Holiday balances and place the total just to the right of the next adjacent cell with a zero value. Essentially this will allow me to take the totals that (based on terms) would have drafted on a weekend or holiday and shift them all to the next available banking day.

bank day	Holiday

<colgroup><col><col></colgroup><tbody>
</tbody>

Draft Date	banking day?	Banking Day?	Corrected Draft Date	bank day	Holiday
1/11/18	0	YES	1/1/18	19,000	-
1/12/18	0	YES		19,001	-
1/13/18	1	NO		-	19,002
1/14/18	1	NO		-	19,003
1/15/18	1	NO		-	19,004
1/16/18	0	YES		19,005	-
1/17/18	0	YES		19,006	-
1/18/18	0	YES		19,007	-
1/19/18	0	YES		19,008	-

<colgroup><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Peter_SSs]

I'm not sure if you grasped what I was suggesting or, perhaps more likely, I am not grasping what you are trying to achieve.
Instead of marking the problem days & then adjusting, I was suggesting calculating the adjusted Draft Date directly as shown below.

Note that my dates are in d/m/y format & I have made up an extra holiday in column J to show the adjustment also in row 10.

Excel Workbook
	B	G	I	J
1			Terms (Days) ->	10
2	Date	Draft Date		Holidays
3	1/01/2018	11/01/2018		15/01/2018
4	2/01/2018	12/01/2018		18/01/2018
5	3/01/2018	16/01/2018
6	4/01/2018	16/01/2018
7	5/01/2018	16/01/2018
8	6/01/2018	16/01/2018
9	7/01/2018	17/01/2018
10	8/01/2018	19/01/2018
Avoid Holidays

 

[bullock2270]

Instead of marking the problem days & then adjusting, I was suggesting calculating the adjusted Draft Date directly as shown below.

Peter,
Thank You! That worked Perfectly!

 

[Peter_SSs]

Peter,
Thank You! That worked Perfectly!

You're welcome.

BTW, you will see that I have removed much of the quote from your post. Best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only, or just enough so we know who you are referring to.