Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time punch & hours calculations
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! -- heyredone |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time punch & hours calculations
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: =n2time(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. "heyredone" wrote in message ... 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! -- heyredone |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating duplicates within range of time punch data | Excel Worksheet Functions | |||
Finding duplicate time punch data | Excel Discussion (Misc queries) | |||
Finding duplicate time punch entries | Excel Discussion (Misc queries) | |||
using if statement to subtract 24 hours from time still shows as a negative time from both responses under 24 hours | Excel Worksheet Functions | |||
Hours Calculations | New Users to Excel |