Formula 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  |  Permalink  |   Subscribe now!

Filed Under: Excel, Formulas
Tags: Excel formula, time difference

This post has 530 views since January 23, 2008 – 3:44 pm.