Predictive ACH Calandar sensitive to Banking Holidays and Weekend Days

[bullock2270]

[TABLE="width: 567"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1=No, 0=Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]Status[/TD]
[TD]OPEN[/TD]
[TD]BOL #[/TD]
[TD]Invoice TTL[/TD]
[TD]Draft Date[/TD]
[TD]Add Days[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD="align: right"]1/1/18[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/11/18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Tuesday[/TD]
[TD="align: right"]1/2/18[/TD]
[TD]Y[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/12/18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Wednesday[/TD]
[TD="align: right"]1/3/18[/TD]
[TD]Y[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/13/18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]Thursday[/TD]
[TD="align: right"]1/4/18[/TD]
[TD]Y[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/14/18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]Friday[/TD]
[TD="align: right"]1/5/18[/TD]
[TD]Y[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/15/18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]Saturday[/TD]
[TD="align: right"]1/6/18[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/16/18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Sunday[/TD]
[TD="align: right"]1/7/18[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/17/18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Monday[/TD]
[TD="align: right"]1/8/18[/TD]
[TD]Y[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/18/18[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

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.
[TABLE="width: 152"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]bank day[/TD]
[TD]Holiday[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 544"]
<colgroup><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Draft Date[/TD]
[TD]banking day?[/TD]
[TD]Banking Day?[/TD]
[TD]Corrected Draft Date[/TD]
[TD]bank day[/TD]
[TD]Holiday[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/18[/TD]
[TD]0[/TD]
[TD]YES[/TD]
[TD]1/1/18[/TD]
[TD] 19,000[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]1/12/18[/TD]
[TD]0[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD] 19,001[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]1/13/18[/TD]
[TD]1[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD] - [/TD]
[TD] 19,002[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/18[/TD]
[TD]1[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD] - [/TD]
[TD] 19,003[/TD]
[/TR]
[TR]
[TD="align: right"]1/15/18[/TD]
[TD]1[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD] - [/TD]
[TD] 19,004[/TD]
[/TR]
[TR]
[TD="align: right"]1/16/18[/TD]
[TD]0[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD] 19,005[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]1/17/18[/TD]
[TD]0[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD] 19,006[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]1/18/18[/TD]
[TD]0[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD] 19,007[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD="align: right"]1/19/18[/TD]
[TD]0[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD] 19,008[/TD]
[TD] - [/TD]
[/TR]
</tbody>[/TABLE]

 

[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.