calculate time for employees in 24 hour operation

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

[daddylonglegs]

With start time in A1 and end time in B1 either

=B1-A1+(A1>B1)

or

=MOD(B1-A1,1)

[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

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

[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)

Remeber to set the cell formats to 24 hour time.

Sincerely,
Leith Ross