Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Formula to determine refined ticket duration

Posted on 2013-06-18
2
Medium Priority
?
345 Views
Last Modified: 2013-06-19
I use MS SQL Management Studio to pull queries from our ticketing system. Attached is a sample of the data so you can see the formats for each column.

I need to do add a column showing the 'SLA duration' which would subtract the suspend hours from the total duration. Total duration should be the service restore date/time minus the create date/time. I can subtract the time stamps but adding in the suspend hours to the equation messes it up.

The SLA duration should display in a number with decimal

Something like:
[(service restored date/time) - (create date/time)] - Suspend Hours

Current format when exported with headers
service restored date/time - custom
create date/time - custom
suspend hours - general

Note: I have also tried to import this into PowerPivot but am not able to convert the suspend hours to a number. Also, I need to be able to do this with a formula, rather than vb.
Restore-Time.xlsx
0
Comment
Question by:awp2012
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 39257989
Try this formula for row 2 to get a decimal number of hours

=(D2-B2)*24-N(E2)

format as number and copy down column

...alternatively if you want the value as a time value (e.g. 2:30 instead of 2.5) then use this formula

=D2-B2-N(E2)/24

and custom format as [h]:mm, see attached for both versions

regards, barry
Restore-Time-barry.xlsx
0
 

Author Closing Comment

by:awp2012
ID: 39259792
Thank you Barry! Excellent!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question