Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how do I calculate hours using one cell only
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 |
#2
|
|||
|
|||
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. "James Fullmer" <James wrote in message ... 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 |
#3
|
|||
|
|||
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 "James Fullmer" <James wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate difference in time spanning a day, during office hours o | Excel Discussion (Misc queries) | |||
calculate hours just can't figure it out | Excel Worksheet Functions | |||
CALCULATE HOURS WORKED | Excel Discussion (Misc queries) | |||
X IN A CELL TO CALCULATE A FORMULA | Excel Worksheet Functions | |||
Cell does not calculate automaticaly | Excel Discussion (Misc queries) |