MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Timesheet Problems

Posted by laura on February 16, 2001 5:30 AM

Okay, I have 2 frustrating problems. Well three. The first is that I seem to have to make a payroll program at my work. I get raw data on how many minutes an employee has worked per hour, (eg 6:00-7:00=0:04:39) which means roughly that they've clocked in at 6:54, and then it follows to 15:00-4:00=0:01:05. I have 60 employees that I have to enter information for every day. Is there a formula that would allow me to even enter data such as 7:00 - 0:04:39? I can't seem to figure a way to work that out! Secondly, I need to create an ongoing monthly average table. Before data is entered into a function, it auto-populates with all zero's and I can't seen to get an average without them. COUNTIF >O doesn't seem to work. Any suggestions??

Posted by Aladin Akyurek on February 16, 2001 2:25 PM

Can't resist to ask: care to explain how your time data look like?
Do you have a column for times at which the employees clock in, another at which they clock out? If so, what is format in which these are entered?


Posted by laura on February 19, 2001 1:03 AM

=(out-in)+IF(in>out,1)*24 This formula gives me the correct difference in time even if it's an overnight shift. At the end of each week, I have the total number of hours worked per week and per pay period. The pay periods are separated by colour. I've totaled the number of hours worked per day for future scheduling purposes. At the beginning of the table I have a summary of total hours worked, and I'm trying to have a summary of average length of shift (so that I can see who is usually late, and who usually works long hours.