Power of Microsoft Office

# Excel Tip: Calculate Difference in Hours between Two Dates

From time to time, people approach me with an Excel problem that looks easy to calculate while, unfortunately, there’s no straightforward solution to it. The problem is calculating the difference (in hours) between two date-time formatted cells.

Example:

Cell A1: 2/15/2012  10:00:00 AM

Cell B1: 2/18/2012  08:00:00 PM

What is the elapsed time between both dates (in hours)?

You can get the answer by using the below formula in cell C1:

= INT(B1-A1)*24+(((B1-A1)-INT(B1-A1))/0.04166666)

The result will be: 82 HRS Formula rationale:

Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. So, an hour is about 0.041666 of the 0.99999.

Hence, with multiplying the integer part of the date serial number by 24 (to get number of hours for the whole elapsed days) and adding the result to the number of hours represented in the fraction part (the fraction part divided by 0.014666) you get the total number of elapsed hours between both dates.

### 10 replies »

1. Jan Praet says:

You are a genius, helped me really well. There are thousands of posts around calculating the time elapsed between two dates, yet none of them grasp the essance like you did, from a mathematical perspective. Thanks again for posting this

Like

• Lexi says:

FANTASTIC!

Like

2. wmphotouk says:

I think you can also do (DATE1 – DATE2)*24, since if you just do DATE1 – DATE2 then you get an answer in decimal days.

Like

3. Wouter says:

It looks fine, but for some regions you would like to take into account the daylight saving time.

Like

4. sunilkumar says:

This really helped me.Struggled with all the formulas finally this worked

Like

5. Jaap 't Hooft says:

Indeed one of the more inteligent and simple way to do this. Congrats.

Like

6. Razvan says:

thanks

Like

7. Sanchez Frometa says:

You are a genius. Straight forward and simple. Thank you so much for your help!!!

Like

8. suchithra says:

Thank u so much

Like

9. ali abbas rajput says:

wa g wa great tip JAZAK ALLAH

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.