Hours Calculation Function (Excel 2002)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

The formula in C1 needs to be:

=(B1-A1)*24

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Steve

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

[indent]

---

The (B1-A1)*24 formula doesn’t work if you were to work from 4pm to midnight

---

[/indent]
Well – yes, it does. See attached.

Tony.

Reply | Quote

[indent]

---

The (B1-A1)*24 formula doesn’t work if you were to work from 4pm to midnight

---

[/indent]
Well – yes, it does. See attached.

Tony.

Reply | Quote

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)

Reply | Quote

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

Steve

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

The formula in C1 needs to be:

=(B1-A1)*24

Reply | Quote

C1 should be:
(B1-A1)*24

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

Steve

Reply | Quote

C1 should be:
(B1-A1)*24

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

Steve

Reply | Quote

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.

Reply | Quote