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

psycoperl

Active Member
Joined
Oct 23, 2007
Messages
287
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
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
ABCDEFGH
1CycleCycle Length (Days)Period StartPeriod End Admissions Request DeadlineAppointment Processing StartInvite SentReminder Sent
2A1003Fri 2021-Feb-26 12:01 AMSun 2021-Feb-28 11:59 PMWed 2021-Feb-24 05:01 PMThu 2021-Feb-25Thu 2021-Feb-25 11:30 PMSat 2021-Feb-27 11:30 AM
3B2003Thu 2021-Mar-11 12:01 AMSat 2021-Mar-13 11:59 PMTue 2021-Mar-09 05:01 PMWed 2021-Mar-10Wed 2021-Mar-10 11:30 PMFri 2021-Mar-12 11:30 AM
4C3003Fri 2021-Mar-26 12:01 AMSun 2021-Mar-28 11:59 PMWed 2021-Mar-24 05:01 PMThu 2021-Mar-25Thu 2021-Mar-25 11:30 PMSat 2021-Mar-27 11:30 AM
5D4003Fri 2021-Apr-09 12:01 AMSun 2021-Apr-11 11:59 PMWed 2021-Apr-07 05:01 PMThu 2021-Apr-08Thu 2021-Apr-08 11:30 PMSat 2021-Apr-10 11:30 AM
6E5003Wed 2021-Apr-28 12:01 AMFri 2021-Apr-30 11:59 PMMon 2021-Apr-26 05:01 PMTue 2021-Apr-27Tue 2021-Apr-27 11:30 PMThu 2021-Apr-29 11:30 AM
7F6003Sat 2021-May-08 12:01 AMMon 2021-May-10 11:59 PMThu 2021-May-06 05:01 PMFri 2021-May-07Fri 2021-May-07 11:30 PMSun 2021-May-09 11:30 AM
8G7013Tue 2021-May-25 12:01 AMThu 2021-May-27 11:59 PMSun 2021-May-23 05:01 PMMon 2021-May-24Mon 2021-May-24 11:30 PMWed 2021-May-26 11:30 AM
9H8003Fri 2021-Jun-11 12:01 AMSun 2021-Jun-13 11:59 PMWed 2021-Jun-09 05:01 PMThu 2021-Jun-10Thu 2021-Jun-10 11:30 PMSat 2021-Jun-12 11:30 AM
10J9003Mon 2021-Jun-28 12:01 AMWed 2021-Jun-30 11:59 PMSat 2021-Jun-26 05:01 PMSun 2021-Jun-27Sun 2021-Jun-27 11:30 PMTue 2021-Jun-29 11:30 AM
11K1103Mon 2021-Jul-12 12:01 AMWed 2021-Jul-14 11:59 PMSat 2021-Jul-10 05:01 PMSun 2021-Jul-11Sun 2021-Jul-11 11:30 PMTue 2021-Jul-13 11:30 AM
Sheet1
Cell Formulas
RangeFormula
D2:D11D2=C2+B2-TIME(0,2,0)
E2:E11E2=C2-2+TIME(17,0,0)
F2:F11F2=E2+TIME(6,59,0)
G2:G11G2=C2-TIME(0,31,0)
H2:H11H2=G2+(0.5*B2)
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
Try these in E2 and F2 respectively,
Excel Formula:
=WORKDAY(G2,-2)+"17:01"
Excel Formula:
=WORKDAY(E2,1)
 
Solution

psycoperl

Active Member
Joined
Oct 23, 2007
Messages
287
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
Great! Thank you... this helped and worked greatly... No longer do I have to sit there and manually calculate this :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,152
Members
416,224
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top