Exceljet

Quick, clean, and to the point

Issue response time calculation

Excel formula: Issue response time calculation
Generic formula 
=B5+VLOOKUP(value,table,column,0)
Explanation 

To calculate a response time based on an assigned status like "urgent" or "critical", you can build a simple table with status details, then use VLOOKUP to lookup look up and calculate the the "target time" - the time by which a response is due.

In the example shown, the formula in D5 is:

=B5+VLOOKUP(C5,$F$5:$G$7,2,0)

How this formula works

Status labels and response times are stored in a separate table in F5:G7. When a matching label is entered in column C, VLOOKUP is used to retrieve the appropriate response time:

VLOOKUP(C5,$F$5:$G$7,2,0)

The reference to F5:G7 is absolute so that the formula can be copied down the table. Column index is provided as 2 to retrieve response times, and matching is set to "exact" by using zero for the last argument.

The response returned by VLOOKUP is then added directly to the datetime value in column B.

Because response times in column G are entered in standard Excel time format (2:00, 8:00, etc.) these time values can be added directly without any conversion, and Excel displays the calculated target time in column D.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.