Calculate and populate duration (days) into cell 3 from cell 1 & 2 that contains specific words

[sauce]

Hi All,

Apologies if i'm not posting in the correct section. I tried to find the most appropriate discussion thread.

This is quite a complicated one (well for me at least) so i'll really be impressed if this can be solved.

What i'm trying to do is calculate and populate the SLA Deadline cell (in date format).
this can be calculated by searching the Ticket type cell for a specific word and numerical value and adding the number of days attributed to the combination of the word and number to the Created Date. The answer must populate in the SLA Deadline column

If Ticket type contains "bug fix" and "4" then add 0 working days to Created Date
If Ticket type contains "bug fix" and "5" then add 1 working days to Created Date


So for example:

if Ticket type contains the words "bug fix" and "5"
then SLA Date = 2019/01/02 (as it adds +1 [working days] to the created date)

Working Days = Mon-Fri

What Formula would i use in the SLA Deadline cells to achieve the below result as an example?

Ticket Type	Created Date	SLA Deadline
bug fix | Group - Availability & Fill Rate | 4 <tbody> </tbody>	2019/01/01	2019/01/01
bug fix | Group - Availability & Fill Rate | 5	2019/01/01	2019/01/02

<tbody>
</tbody>

hope this makes sense

 

[mattmickle]

is the word "bug fix" and the number always in the same location?
ie. xxxxbugfixxx4xxx where bug fix is always starting in the fifth position and the number is always in thirteenth positon?

or is it random within a text string?

 

[sauce]

bug fix will always be in the same position at the very start of a text string, however the number will be in different positions

 

[sauce]

this did the trick if anyone wants to know

=IF(ISNUMBER(SEARCH("Bug Fix | * | 4",J7)),WORKDAY(D7,0),IF(ISNUMBER(SEARCH("Bug Fix | * | 5",J7)),WORKDAY(D7,1),""))

now does anyone know how i would change the text in the answer cell to RED if date is less than TODAY?