# Thread: Hours Calculation Function (Excel 2002)

1. ## Hours Calculation Function (Excel 2002)

Hey people, I was just wondering if anyone had an already made copy of an Excel function that would calculate the difference between two times. For instance:
A1: 8:30 AM B1: 6:00 PM C1: =9.5
I know approximately how to do this, but I know it would take me a long time (nested ifs and right functions). Just wanted to check if anyone else has made this already. Thanks,

-naut

2. ## Re: Hours Calculation Function (Excel 2002)

Format col A & B to the appropriate time format and format col C to "number" format with two decimal places. In C1 enter =B1-A1*24
Excel stores times based on a percentage of the day so 6:00 PM equals 0.75 to Excel. So by figuring out the difference and mulitplying by 24 it gives you the elapsed time in a numeric format instead of a time format.

3. ## Re: Hours Calculation Function (Excel 2002)

The formula in C1 needs to be:

<pre>=(B1-A1)*24
</pre>

4. ## Re: Hours Calculation Function (Excel 2002)

C1 should be:
(B1-A1)*24

since you want to SUBTRACT before you multiply. Your calc will subtract the A1*24 from B1.

Steve

5. ## Re: Hours Calculation Function (Excel 2002)

D' Oh! Thanks for correcting my brain cramp!!

6. ## Re: Hours Calculation Function (Excel 2002)

I managed to make one on my own, it is very different compare to what you have:
=(HOUR(B1)-12) + (12-HOUR(A1)) - (MINUTE(A1) /60) + (MINUTE(B1) / 60)
* where A1 is the start time and B1 is the end time. (eg. A1 = 8:00 AM, B1 = 6:30 PM)
Thanks for the input.

7. ## Re: Hours Calculation Function (Excel 2002)

The one(s) listed above also will take into account days, and seconds and is alot easier.

Steve

8. ## Re: Hours Calculation Function (Excel 2002)

This is a pretty old thread, but I just figured I post this little update incase anyone else refferes to it. The (B1-A1)*24 formula doesn't work if you were to work from 4pm to midnight. Here is a fix incase anyone ever needs it:
=IF((B1-A1)*24>=0, (B1-A1)*24, (B1-A1)*24+24)

9. ## Re: Hours Calculation Function (Excel 2002)

If you add the date to the time, then the (B1-A1)*24 will work just file.

10. ## Re: Hours Calculation Function (Excel 2002)

<hr>The (B1-A1)*24 formula doesn't work if you were to work from 4pm to midnight<hr>
Well - yes, it does. See attached.

Tony.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•