calculate time for employees in 24 hour operation

H

HoweRich

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

daddylonglegs

With startt time in A1 and end time in B1 either

=B1-A1+(A1>B1)

or

=MOD(B1-A1,1)
 
T

Toppers

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
 
L

Leith Ross

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top