Results 1 to 9 of 9

Calculate Number of Releases Based on Date and Named Range

Threaded View

  1. #1
    Registered User
    Join Date
    05-04-2018
    Location
    MD
    MS-Off Ver
    2016
    Posts
    20

    Calculate Number of Releases Based on Date and Named Range

    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.
    Attached Files Attached Files
    Last edited by mac_squared; 08-19-2019 at 01:29 PM. Reason: marking this as solved.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculate Seniority Number Based on Date Of Birth and Date of Joining
    By deepanc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-20-2019, 11:28 AM
  2. [SOLVED] Conditional Formatting Date based on a Named Range set of Dates
    By coreytroy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-07-2018, 02:46 AM
  3. [SOLVED] VLOOKUP with the ability to select named range based on a date in another cell
    By Webbers in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 04-22-2016, 02:00 PM
  4. Replies: 1
    Last Post: 07-18-2013, 10:11 AM
  5. Replies: 0
    Last Post: 03-21-2013, 12:22 PM
  6. [SOLVED] Calculate date based on number of weeks or months after start date
    By wotsup in forum Excel General
    Replies: 3
    Last Post: 08-17-2012, 06:00 AM
  7. [SOLVED] Dynamic named range based on a start date and end date
    By Gary Lockton in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-06-2012, 07:26 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1