Adding business days to a business day formula

Hi everyone,

I'm hopeful someone can have an answer to this problem I'm been wrestling with.

I’d attach the spreadsheet but I keep on getting an error message when I try so I’ll describe the problem instead.

The sheet tracks workflow – basically, checking that the work is completed within our own internal 10 business day target (SLA).

The original formula was =WORKDAY((A5),Data!$B$3,Data!$G$3:$G$14)

A5 has the date received
Data!$B$3 looks up the SLA days
Data!$G$3:$G$14 has the public holidays. Fortunately we work a Monday to Friday week.

That was doing fine until I realised that the person who requested that we do the work may grant us extra days to do it (usually while they supply something they forgot).

So, I added an additional column (D) to record the additional time permitted and then add those business days to the date calculated.

I added D into the formula as below—
=WORKDAY((A5+D5),Data!$B$3,Data!$G$3:$G$14)

But that formula treats the additional days as calendar days.

Does anyone have an idea as to how to change the formula so that the additional days In D are treated as just business days?

I could just tell everyone that they need to think business days initially as that's what our SLA uses and then calendar days for an extension, but I'd love to be able for them to just think business days all the time.

Thanks,

Tim

Brilliant, absolutely brilliant! That's so much.

I can see the logic now - thanks!

Hi FDibbins,

I'm hopeful you could help with the 2nd part of the problem. I thought I could apply your answer to it, but I'm a bit lost.

The formula is =WORKDAY(E5,INDEX(Data!$D$3:$D$24,MATCH(N5,Data!$A$3:$A$24,0)),Data!$G$3:$G$14)

E5 is the start date
Data!$D$3:$D$24 is the timeframe related to the activities at Data!$A$3:$A$24 and
Data!$G$3:$G$14 contains the holidays
on the spreadsheet, the extra business days are at H5

I tried E5+H5 but it treated them as calendar days.

It's the same situation – I need to add those days into the system – and have them treated as business days.

This one seems somewhat more tricky – do you have answer or should I posted as a separate question?

Tim

And thanks from me, as well.

Tim