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

sauce

New Member
Joined
Oct 30, 2018
Messages
5
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?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ticket Type[/TD]
[TD]Created Date[/TD]
[TD]SLA Deadline[/TD]
[/TR]
[TR]
[TD][TABLE="width: 734"]
<tbody>[TR]
[TD="width: 734"]bug fix | Group - Availability & Fill Rate | 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2019/01/01[/TD]
[TD]2019/01/01[/TD]
[/TR]
[TR]
[TD]bug fix | Group - Availability & Fill Rate | 5[/TD]
[TD]2019/01/01[/TD]
[TD]2019/01/02[/TD]
[/TR]
</tbody>[/TABLE]

hope this makes sense
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?
 
Last edited:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

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
Back
Top