MrExcel Publishing
Your One Stop for Excel Tips & Solutions

I need help


Posted by John Andan on February 15, 2002 4:54 PM

How can I get the total hours of a person who works from 8:30am to 5:30pm.

For Example: 8:30am - 5:30pm that's 9hours minus 1hour(break)= 8hours. what is the formula for this problem ?

Thank's

John


Posted by Paul B on February 15, 2002 5:23 PM

Start time in A1,end time in B1 lunch in C1 in D1 put =B1-A1-C1 format as custom [h]:mm

Posted by Jake on February 16, 2002 2:33 PM

I have a similar problem, but total hours worked 18:45pm to 08:30am (night shift) My employer calculates the time (00:00) into total in decimal eg: 13.75. How do I do the same? Anyone know a formula?

Posted by JC3 on February 19, 2002 9:41 AM

Assuming A1 = Start time, B1 = End Time and C1 = Lunch duration (all in h:mm format)
Put the following in cell D1
=IF(A1>B1,B1-A1-C1+1,B1-A1-C1)*24

Posted by Dave Gibson on February 19, 2002 10:08 AM

You need to break it up into two calculations; subtract the start time from midnight (24:00), then add that to the end time to get the total time. To convert that to a decimal time, in a cell formatted as General, multiply the total time by 24.