Add-Ins
Blogs I Read
ContactFormula for Date/Time Subtraction in Excel
January 23, 2008 – 3:44 pm by JP
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
Print This Post
|
Email This Post
|
Subscribe to Posts Feed
|
Subscribe to Comments
Filed Under: Excel, Formulas
Tags: Excel formula, subtraction, time difference
2 Responses to “Formula for Date/Time Subtraction in Excel”:
Brilliant!
Cheers
By Dan on Mar 21, 2008 at 4:45 pm
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
By JP on Mar 22, 2008 at 12:42 pm