Issue response time calculation

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.

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.