Solved

Formula to determine refined ticket duration

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

828 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