how do I calculate hours using one cell only

J

James Fullmer

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
 
A

Alan

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.
 
B

Biff

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
 
Top