Solved

Time to Resolve

Posted on 2014-04-26
3
152 Views
Last Modified: 2014-04-27
I have 2 cells with a opened time and a closed time i want to minus opened from closed and get the total number of hours. each cell is formated like this 2013/05/31 19:49:17. When i minus one from the other the days done show just the hours. Also need error checking in the equation so I can get an average time from a pivot table. I am using excel 2013
0
Comment
Question by:titanium123
[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
  • Learn & ask questions
  • 2
3 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 40025397
Excel has DATEDIF function, but it can return only days, months and years, so if you can use macros, add this function to new module:
Public Function hdiff(S As Range, E As Range) As Double
If IsNull(S) Or S = 0 Or IsNull(E) Or E = 0 Then
    hdiff = 0
Else
    hdiff = DateDiff("s", S, E) / 3600 'Calculate difference in seconds and convert to decimal hours
End If
End Function

Open in new window

and call it in cell:
=hdiff(Start Date, End Date)
0
 

Author Comment

by:titanium123
ID: 40025444
Thanks als315 that works just pefect
0
 

Author Closing Comment

by:titanium123
ID: 40025445
Great solution
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

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…
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…
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

738 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