Solved

Time to Resolve

Posted on 2014-04-26
152 Views
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
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
• 2

LVL 40

Accepted Solution

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
``````
and call it in cell:
=hdiff(Start Date, End Date)
0

Author Comment

ID: 40025444
Thanks als315 that works just pefect
0

Author Closing Comment

ID: 40025445
Great solution
0

Featured Post

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…