I have a tricky problem to solve with Excel, and am hoping you all can help.
I have a spreadsheet for Service Level Agreements, where based on the Priority of the issue, the issue needs to be solved within a certain number of quarterly releases. If priority = High solve within 2 releases. If priority = Medium solve within 3 releases. Currently, based on the priority I am adding 180 days for High, and adding 270 days for Medium and looking for the closest date within a named range that contains all of the release dates.
The issue is, the release dates are not always a set number apart, the difference between release dates can be 40, 42, and 105 days apart which total 3 releases, so if the issue was a high severity, the formula would add 180 days and find the 3rd release date as opposed to the second release date.
Is there a way, based on the created date and the priority, to count the number of releases in a named range and determine when the item should be resolved?
For example:
Currently
Capture.PNG This adds a number of days, and finds the closest date on the named range. Since the number of days can vary between releases, this is not ideal and some releases can be overlooked.
Desired result
Capture2.PNG This counts the number of releases on the named range. The first row counts the 2/1 as the first release, and the 2/25 as the second release. So that's when the target release should be. Same for the medium row, it counts the 2/25 release, 3/15, and 4/1, a total of three releases.
The attachment has the formulas I'm currently using. Column C adds the days according to priority, and column D uses a formula to find the closest date from a named range in column J.
Bookmarks