Results 1 to 10 of 10

20030924, 12:55 #1
 Join Date
 Sep 2003
 Location
 Toronto, Ontario, Canada
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20030924, 13:36 #2
 Join Date
 Jan 2002
 Location
 Trenton, Ontario
 Posts
 175
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 =B1A1*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.

20030924, 13:49 #3
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Hours Calculation Function (Excel 2002)
The formula in C1 needs to be:
<pre>=(B1A1)*24
</pre>
Legare Coleman

20030924, 13:50 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Hours Calculation Function (Excel 2002)
C1 should be:
(B1A1)*24
since you want to SUBTRACT before you multiply. Your calc will subtract the A1*24 from B1.
Steve

20030924, 13:55 #5
 Join Date
 Jan 2002
 Location
 Trenton, Ontario
 Posts
 175
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Hours Calculation Function (Excel 2002)
D' Oh! Thanks for correcting my brain cramp!!

20030924, 14:24 #6
 Join Date
 Sep 2003
 Location
 Toronto, Ontario, Canada
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
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) + (12HOUR(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.

20030924, 17:11 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Hours Calculation Function (Excel 2002)
The one(s) listed above also will take into account days, and seconds and is alot easier.
Steve

20031106, 14:21 #8
 Join Date
 Sep 2003
 Location
 Toronto, Ontario, Canada
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 (B1A1)*24 formula doesn't work if you were to work from 4pm to midnight. Here is a fix incase anyone ever needs it:
=IF((B1A1)*24>=0, (B1A1)*24, (B1A1)*24+24)

20031106, 14:28 #9
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Hours Calculation Function (Excel 2002)
If you add the date to the time, then the (B1A1)*24 will work just file.
Legare Coleman

20031106, 14:38 #10
 Join Date
 Mar 2003
 Location
 Beddau, Mid Glamorgan, Wales
 Posts
 289
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Hours Calculation Function (Excel 2002)
<hr>The (B1A1)*24 formula doesn't work if you were to work from 4pm to midnight<hr>Well  yes, it does. See attached.
Tony.Regards,
Tony
[s] [/s]
www.SylviArtist.com