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
Solved

Time to Resolve

Posted on 2014-04-26
3
147 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
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

839 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