Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Formula to determine refined ticket duration

Posted on 2013-06-18
Medium Priority
343 Views
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
Question by:awp2012
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Accepted Solution

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

ID: 39259792
Thank you Barry! Excellent!
0

## Featured Post

Question has a verified solution.

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

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.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month6 days, 5 hours left to enroll