How to do calculations on dates that return VALID business Days?

[psycoperl]

Good Morning, I hope that this finds you all safe and well:

I am working on developing a Excel Sheet that can be used to build task schedules.

What I would like to know how can I get formulas that would return values on valid working days.

While Columns C,D,G and H can be on non-business days as the process is automated-prescheduled (I,e, Weekends) Columns E and F require Human Office Work that needs to be done during business days.

I would like to have Column E be set to 5:01PM two business days before the value of Column G, and Column F being set to the business day following Column E.

How can I do this programmatically?

Thanks in advance for your guidance.

Fall 2021 BW Ticketing Timeline.xlsx
	A	B	C	D	E	F	G	H
1	Cycle	Cycle Length (Days)	Period Start	Period End	Admissions Request Deadline	Appointment Processing Start	Invite Sent	Reminder Sent
2	A100	3	Fri 2021-Feb-26 12:01 AM	Sun 2021-Feb-28 11:59 PM	Wed 2021-Feb-24 05:01 PM	Thu 2021-Feb-25	Thu 2021-Feb-25 11:30 PM	Sat 2021-Feb-27 11:30 AM
3	B200	3	Thu 2021-Mar-11 12:01 AM	Sat 2021-Mar-13 11:59 PM	Tue 2021-Mar-09 05:01 PM	Wed 2021-Mar-10	Wed 2021-Mar-10 11:30 PM	Fri 2021-Mar-12 11:30 AM
4	C300	3	Fri 2021-Mar-26 12:01 AM	Sun 2021-Mar-28 11:59 PM	Wed 2021-Mar-24 05:01 PM	Thu 2021-Mar-25	Thu 2021-Mar-25 11:30 PM	Sat 2021-Mar-27 11:30 AM
5	D400	3	Fri 2021-Apr-09 12:01 AM	Sun 2021-Apr-11 11:59 PM	Wed 2021-Apr-07 05:01 PM	Thu 2021-Apr-08	Thu 2021-Apr-08 11:30 PM	Sat 2021-Apr-10 11:30 AM
6	E500	3	Wed 2021-Apr-28 12:01 AM	Fri 2021-Apr-30 11:59 PM	Mon 2021-Apr-26 05:01 PM	Tue 2021-Apr-27	Tue 2021-Apr-27 11:30 PM	Thu 2021-Apr-29 11:30 AM
7	F600	3	Sat 2021-May-08 12:01 AM	Mon 2021-May-10 11:59 PM	Thu 2021-May-06 05:01 PM	Fri 2021-May-07	Fri 2021-May-07 11:30 PM	Sun 2021-May-09 11:30 AM
8	G701	3	Tue 2021-May-25 12:01 AM	Thu 2021-May-27 11:59 PM	Sun 2021-May-23 05:01 PM	Mon 2021-May-24	Mon 2021-May-24 11:30 PM	Wed 2021-May-26 11:30 AM
9	H800	3	Fri 2021-Jun-11 12:01 AM	Sun 2021-Jun-13 11:59 PM	Wed 2021-Jun-09 05:01 PM	Thu 2021-Jun-10	Thu 2021-Jun-10 11:30 PM	Sat 2021-Jun-12 11:30 AM
10	J900	3	Mon 2021-Jun-28 12:01 AM	Wed 2021-Jun-30 11:59 PM	Sat 2021-Jun-26 05:01 PM	Sun 2021-Jun-27	Sun 2021-Jun-27 11:30 PM	Tue 2021-Jun-29 11:30 AM
11	K110	3	Mon 2021-Jul-12 12:01 AM	Wed 2021-Jul-14 11:59 PM	Sat 2021-Jul-10 05:01 PM	Sun 2021-Jul-11	Sun 2021-Jul-11 11:30 PM	Tue 2021-Jul-13 11:30 AM
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=C2+B2-TIME(0,2,0)
E2:E11	E2	=C2-2+TIME(17,0,0)
F2:F11	F2	=E2+TIME(6,59,0)
G2:G11	G2	=C2-TIME(0,31,0)
H2:H11	H2	=G2+(0.5*B2)

 

[jasonb75]

Try these in E2 and F2 respectively,

=WORKDAY(G2,-2)+"17:01"

=WORKDAY(E2,1)

 

[psycoperl]

Great! Thank you... this helped and worked greatly... No longer do I have to sit there and manually calculate this