We are using the Aloha POS in our restaurant. The POS has a time clock
funtion as part of the operating system. Each employee clocks in and out for
their shift worked. Aloha stores all the punches for each employee in our 24
hour restaurant. At the end of the pay period I export a Coconut Code file
(.txt ) out to a Excel work book. This workbook will calcuate the hours
worked for each shift and give the total hours worked for the pay period in a
transmittal form to our accountant. The problem I am having. I have not been
able to write a formula that checks and calculates the hours worked for the
employess that work the overnight shift. Example : Clock in at 23:00 and
Clock out at 06:00. The hours worked should be 7:00. Excel gives me a #######
error because it is giving a negative time. This formula should be able to
work on all the times not just the overnight times.

With startt time in A1 and end time in B1 either

=B1-A1+(A1B1)

or

=MOD(B1-A1,1)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513773

Assume start time in A1 and finish time in B1 and formatted as h:mm, the
hours worked in (say) c1 is calculated using:

=MOD(B1-A1,1)


HTH

"HoweRich" wrote:

We are using the Aloha POS in our restaurant. The POS has a time clock
funtion as part of the operating system. Each employee clocks in and out for
their shift worked. Aloha stores all the punches for each employee in our 24
hour restaurant. At the end of the pay period I export a Coconut Code file
(.txt ) out to a Excel work book. This workbook will calcuate the hours
worked for each shift and give the total hours worked for the pay period in a
transmittal form to our accountant. The problem I am having. I have not been
able to write a formula that checks and calculates the hours worked for the
employess that work the overnight shift. Example : Clock in at 23:00 and
Clock out at 06:00. The hours worked should be 7:00. Excel gives me a #######
error because it is giving a negative time. This formula should be able to
work on all the times not just the overnight times.

Hello HoweRich,

You can use an IF statement to return the correct number of hours in
any case.
=IF(StartTime<=StopTime, StopTime-StartTime, 24 -StartTime+StopTime)

Or using cells A1 for StartTime and B1 for StopTime...
=If(A1<=B1, B1-A1, 24-A1+B1)

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=513773