Calculating Next Working Day

[reberryjr]

I have several different categories of work in a spreadsheet, where not all categories have the same SLA for completion. I've been using this formula for the past few months, and there hasn't been an issue, until last Friday. Essentially, a record came in with a start date of 11/22/18. That record has a SLA of 1 day. Because 11/22/18 was a holiday, the SLA date should have been returned as 11/23/18. Instead, this formula is returning the date of 11/24/18, which is a Saturday. I always understood that the Workday function automatically excluded Saturday and Sunday.

Thoughts on where I went wrong?

=WORKDAY(D2,1+(WEEKDAY(D2,1)>6),Variables!D$2:D$11)+COUNTIF(Variables!D$2:D$11,D2)

 

[steve the fish]

It doesnt make a whole lot of sense to me what you have there. If the date in D2 is a saturday you add 2 days. Why? Then you add a countif if the date is in the holiday list. Why? Dont you just need:

=WORKDAY(D2,1,Variables!$D$2:$D$11)

 

[reberryjr]

The 2 days would be added to the Saturday, to start the SLA period on Monday. Essentially, if the record is received on a Saturday, Sunday, or a Holiday listed in the Variables tab; the SLA start date should be the next working day. Hope that helps explain it.

 

[steve the fish]

Thats what the provided formula does. If you add 1 day in a workday formula it provides the next working day which naturally excludes weekends and holidays.

 

[Phuoc]

Try:

=WORKDAY(D2-1,2,Variables!$D$2:$D$11)

 

[Phuoc]

Thoughts on where I went wrong?

=WORKDAY(D2,1+(WEEKDAY(D2,1)>6),Variables!D$2:D$11)+COUNTIF(Variables!D$2:D$11,D2)

Edit:

=WORKDAY(D2,1+Or(WEEKDAY(D2,1)>6,COUNTIF(Variables!D$2:D$11,D2)),Variables!D$2:D$11)

 

[reberryjr]

@Phuoc Thank you! This appears to be working so far!

 

[steve the fish]

How does:

=WORKDAY(D2,1+Or(WEEKDAY(D2,1)>6,COUNTIF(Variables!D$2:D$11,D2)),Variables!D$2:D$11)

do what you asked??

D2 is 11/22/2018
11/22/2018 is in the holiday list.

You wanted to produce:

11/23/2018

That formula doesnt.

 

[reberryjr]

Because 11/22 is in the holiday list, the formula is starting the SLA off on 11/23, with an end result of 11/26, skipping the weekend.

 

[steve the fish]

Ok so what happens if you recieve the SLA date on a weekend? Move to the monday then add a day?