Solved

Formula to determine refined ticket duration

Posted on 2013-06-18
2
338 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 500 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

831 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