Solved

Formula to determine refined ticket duration

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now