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.
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.
In this example, the goal is to calculate the number of days a ticket/case/issue has been open. We start counting on the date a ticket was opened, and stop counting on the date a ticket was closed. If there is no closed date, the ticket is still...
VLOOKUP is an Excel function to look up data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the first...
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.