Working out an SLA (To Include UK Bank Holidays)

[surkdidat]

Hi

I have the following formula, however, I have just noticed, that (rather obviously) it is not taking into account the UK Bank Holidays.

I have saved these in a tab called "Mechanics" in Column I Row 1, downwards).

=IF(IFERROR(
FIND("No target",I35),0)>0,
WORKDAY(TODAY(),10),WORKDAY(G35,
IF(IFERROR(FIND("1 (same-day SLA)",I35),0)>0,2,
IF(IFERROR(FIND("2 (3-day SLA)",I35),0)>0,4,
IF(IFERROR(FIND("3 (6-day SLA)",I35),0)>0,7,7)))))

On the tab this appears (In_Progress)
Column G is the Start Date
Column I is the Tier ( ie "1 (same-day SLA)", "2 (3-day SLA)", or "3 (8-day SLA)"
Column N will contain the formula (presently, as above)


At the moment (as of 13 April) the Tier 3 (an 8-day SLA, runs out on 22 April. However, with Good Friday and Easter Monday, I need 2 Working Days added to this. (Hence the list I have created in the "Mechanics Tab in Column I, which lists the individual dates of all the bank holidays I wish to take into consideration.)

Many thanks

 

[surkdidat]

I have worked the isue out now, by adding the list after WORKDAY, and at the end of the formula (after the 2nd bracket, to include the list!

 

[6StringJazzer]

WORKDAY takes as an optional third argument the range for a list of holidays. Perhaps if you plug that in your existing formula will work. It's a bit difficult to break that formula down or do any testing without seeing your file layout.

WORKDAY(TODAY(),10,Mechanics!I1:I12)