This formula will show the difference (in hours) between two cells with date/time values.
=IF(INT(B2)-INT(A2)<1,24*(mod(b2,1)-mod(a2,1)),((int(b2)-int(a2))*24)+24*(mod(b2,1)-mod(a2,1)))
Press Ctrl-1 to format the cell, on the Number tab choose "Custom," enter this format:
####.0# "hours"
The cell will show the number of hours passed between both dates/times, neatly formatted with a custom format displaying the word "hours" in the cell.
For example, suppose you have "1/12/2008 10:30 AM" in cell A2 and "1/13/08 11:00 AM" in cell B2 in the same worksheet. The formula cell will display "24.5 hours". You can display the cells in any format you wish (i.e. General, Date, etc), as long as they contain real dates/times.
The time value is in decimal format based on hours, so .5 hours = 30 minutes and .75 hours is 45 minutes.
Enjoy,
JP
About JP
8 Comments:
Brilliant!
Cheers
Thanks Dan! FYI I should mention that the formula only calculates raw hours, it doesn't filter by working hours. If you want something like that, check out
http://www.cpearson.com/excel/DateTimeWS.htm
Thx,
JP
u r a genious…. I have been looking for this formula for years…Had worked on the same but didn't get right results…
Thanks a lot!
YM
If you just want the difference in hours:min:secinds:
=TEXT(B2-A2,"hh:mm:ss") or
=TEXT(B2-A2,"hh:mm") or without the seconds
Cheers
Ed.
Still doesn't work for what I would like to do.
Say you have 5 people, each take a few hours (or days) to accomplish a task. (put the 5 names in a column). Then they undergo training to improve their efficiency. Put their time before training in Col2 and after in Col3. Now, measure the difference and then aggregate (i.e. total). Even if you expect the total a positive, some of the individuals might have longer times after training resulting in negative differences, despite the aggregate being positive. How to calculate correctly? I think my boss was saying that Access can do this correctly.
This formula will only work if you had a start time and end time, and needed to calculate how much time had elapsed. It works with dates and times, not elapsed times. You've already got the time differences.
It sounds like all you need is simple math: Col3 minus Col2.
Awesome formula! Is there a way to deduct weekends? It seems like I have seen that formula somewhere before.
Thanks
You mean something like this?
http://www.jpsoftwaretech.com/blog/2009/03/time-difference-testing/