Solved

Time to Resolve

Posted on 2014-04-26
3
139 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 39

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

760 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

18 Experts available now in Live!

Get 1:1 Help Now