Formula for Date/Time Subtraction in Excel

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

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 8 Comment(s) on Formula for Date/Time Subtraction in Excel:

  1. Brilliant!

    Cheers

  2. 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

  3. Yashwant Mahakal writes:

    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

  4. 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.

  5. 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.

  6. Awesome formula! Is there a way to deduct weekends? It seems like I have seen that formula somewhere before.

    Thanks

This article is closed to any future comments.
Random Data Generator