how do I calculate hours using one cell only

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James Fullmer - 28 Aug 2005 02:05 GMT

I am trying to create a schedule that calculates hours worked in a day. I
have several restrictions in space, so would like to have one cell that shows
start and end time, and a second cell that shows hours worked.

For instance
A1 10:00 AM - 5:00 PM
B1 8

I am basically looking for the formula to put in B1

Reply to this Message

Alan - 28 Aug 2005 02:30 GMT

I don't think you can do it in one cell, or at least, not easily. However
with the start time in one cell and the end time in another its just simple
subtraction, subtract the start time from the end time. It gets more
complicated though if the start time is say 22:00 and the end time is 06:00
the next morning so the shift spans two days
Regards,
Alan.

>I am trying to create a schedule that calculates hours worked in a day. I
> have several restrictions in space, so would like to have one cell that
[quoted text clipped - 6 lines]
>
> I am basically looking for the formula to put in B1

Reply to this Message

Biff - 28 Aug 2005 04:22 GMT

Hi!

See which method you might prefer:

A1 = 10:00 AM
B1 = 5:00 PM
C1 = formula:

=(B1-A1+(B1<A1))*24

OR......

A1 = 10:00 AM - 5:00 PM
B1 = formula:

=((SUBSTITUTE(A1,LEFT(A1,FIND("-",A1)+1),"")*1-SUBSTITUTE(A1,RIGHT(A1,FIND("-",A1)-1),"")*1)+(SUBSTITUTE(A1,LEFT(A1,FIND("-",A1)+1),"")*1<SUBSTITUTE(A1,RIGHT(A1,FIND("-",A1)-1),"")*1))*24

Seems like a no brainer to me! <g>

Biff

Reply to this Message