Time punch & hours calculations

[heyredone]

I need to calculate hours increments (less than & more than) from numerous
worksheets of time punch data. How do I do that? My worksheets are
currently formatted as follows:

A: First Name
B: Last Name
C: Badge No.
D: Store
E: Date
F: [purposely left blank]
G: Time punch
H: Time In (using an array formula from data in column G)
I: Time Out (using an array formula from data in column G)
J: Time In (using an array formula from data in column G)
K: Time Out (using an array formula from data in column G)
L & M: [purposely left blank]
N: the formula =IF(ISERROR(I2-H2),"-",I2-H2)
O: the formula =IF(ISERROR(J2-I2),"-",J2-I2)
P: the formula =IF(ISERROR(K2-J2),"-",K2-J2)
Q: the formula =IF(ISERROR(N2+P2),"-",N2+P2
R: the formula =IF(ISERROR(I2-H2+K2-J2),"-",I2-H2+K2-J2) (to confirm column Q)

The data & time punch info. for each employee runs toward, hence, the array
formula for columns H thru K so I can see the punches in & out for each
person on a given day in a left to right fashion. Columns H-K & N-R are
customized to "h:mm:ss AM/PM" and "h:mm:ss" respectively. Beginning in
column T and across, I now need to deterine hours breakdowns such as in a
given workday, did the employee work more than 5 hrs. total, more than 5 hrs.
but less than 5 hrs. & 15 min., more than 6 hrs., 10 hrs., 12 hrs., etc.
These can be true/false formulas. I also then need to make several
calculates such as if the employee worked more than 6 hrs. total, did the
employee punch out for at least 30 minutes between his/her first punch-in and
his/her last punch-out. This can be a true/false formula as well.

Sorry for the long-winded explanation; I hope it makes sense. Can someone
help guide me in the right direction to figure this out? I have tried
several formulas but they don't seem to be accurate.

Thank you so much!

 

[Fred Smith]

The first thing I would to is change your dashes to zeros. Then you can use
the cells in calculation without checking for errors. If you want a dash
displayed whenever there's zero in the cell, use a custom format.

Second, to check if an employee has worked more than 5 hours, use:
=n2>time(5,0,0)

This will give you a true/false result. Hopefully the rest of the formulas
you can come up with on your own. If not, post back with specifics.

Regards,
Fred.