Setting up a Timesheet for workers with overtime.

Hello everyone my name is Tom. I just started as an assistant for a fairly large group of people.
I've fiddled with Google docs a bit but Excel is a whole nother monster this is my first project I've done on excel.

I have a few question I am kind of time sensitive so Instead of researching I was hoping someone would be able to give me some simplified answers.

1) How can I fix the problem with trying to substitute an empty cell in designated areas for 0. I know you can do sum but the cell is also calculated for overtime so right now all i have is substitute " " (space) for 0 wich is kind of annoying. I want the option to put # of hours or S for sick etc. etc..
(Bonus?: It'd be nice if I found a way to add up all the S and V and L on the main expenses table)

2) Is there a way setup that when they come "IN" to work if its after 6am it will put "tardy" in status area then it will copy that incident to another sheet. I'm trying to get everything as automative as possible.

3) I have no idea how I will do this but I wanted to kind of mark people as either journeyman\apprentice\etc and it will add up all journeymen hours to another sheet, or all apprentice hours to another sheet.

My goal is to just have a spreadsheet of mostly everything I need to know about each individual person along as well as them all added up. I saw an attendance record

Any tips and help is GREATLY appreciated! Thank you for atleast giving me your time to read thus far.

I attached a demo without anyone's names if you want a example.



EDIT:

Originally Posted by jason.b75

Using the formula that I suggested to enter the 'Tardy' comment in the status column, you could use

=COUNTIF(December!I16:AC178,"tardy")

to count all instances of 'Tardy' on the sheet, but without some completed (fictional) data, names, etc on the sample, I don't know what will be going where to count occurrences by name.

Alright I've attached a sort of demo. Its for an electricians team and Each person is either a Material hander, Apprentice or Journeyman.
What I hope to achieve Is entering the most minimal amount of data wich will calculate\automate everything else. While also having a simplified summary version for the boss.
What Im trying to do is find some way to mark people as Apprentice or Journeyman so that their overtime\normal hours gets categories into the correct spot on Sheet: Hours and Expenses Table.
I have also attached an Attendance Record DEMO that I used for a very short period of time. The only problem with it is I cant calculate overtime and I cant put specific hours (for when they are tardy)
I may just be over thinking it - But any tips\tricks or suggestions would be greatly appreciated.Attachment 437396 Attachment 437401

Edit: I love how simple the Attendance Record DEMO is but again I cant put hours. I was brain storming and maybe I could make some sort of way that it will put a list of all Tardies on a seperate sheet maybe so when I put L I can put on that other sheet what exactly the time they came in at.

Hi, Tom, welcome to the board.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

I have closed your other thread on this, please contunue here

In G16, et al.:

Please Login or Register  to view this content.

Originally Posted by tommygoesha

Although where you got three is beyond me.

It's beyond me as well now, think I must have refreshed the page as you changed the title on this one and counted it twice, either that or seeing double from the effects of the pre Christmas tipple...

For the comment in the status column, try this in I16, then fill down to I22 and copy to the other ranges as needed.

=IF(MOD(H16,1)>0.25,"Tardy","")

Note that I have used "" and not " " to keep the cell blank when required, using " ", as you have done in G16 can cause problems when you least expect it. (also see post #6 for an alternative formula for G16). An empty cell is 0 by default so there is no need to substitute.

Try to avoid using merged cells as well, which I notice that you have in columns C and D, if you need to reference those cells in a formula at a later point, that can also create problems.

That's about as much as we can help you with until you update your sample file, it would be easier to follow if you entered some fictional data to demonstrate a completed sheet, or at least a couple of people / days worth of data. While we could guess, without a few John Doe's, etc. to see where the names will go in each sheet, it would be guesswork that could be wrong.

Originally Posted by jason.b75

It's beyond me as well now, think I must have refreshed the page as you changed the title on this one and counted it twice, either that or seeing double from the effects of the pre Christmas tipple...

For the comment in the status column, try this in I16, then fill down to I22 and copy to the other ranges as needed.

=IF(MOD(H16,1)>0.25,"Tardy","")

Note that I have used "" and not " " to keep the cell blank when required, using " ", as you have done in G16 can cause problems when you least expect it. (also see post #6 for an alternative formula for G16). An empty cell is 0 by default so there is no need to substitute.

Try to avoid using merged cells as well, which I notice that you have in columns C and D, if you need to reference those cells in a formula at a later point, that can also create problems.

That's about as much as we can help you with until you update your sample file, it would be easier to follow if you entered some fictional data to demonstrate a completed sheet, or at least a couple of people / days worth of data. While we could guess, without a few John Doe's, etc. to see where the names will go in each sheet, it would be guesswork that could be wrong.

Well #6 was exactly what I was looking for. As for the overtime Columns I just didnt know the formula to ignore Text. So in the hour slot I can either put 10 hours (2 hours overtime) or S (for sick) and it wont mess up the calculation.
But I was most curious if there is a way to make an automative list of all Lates onto another sheet.
What I really want to do is have the IN and OUT on a seperate sheet then it automatically Lists tardy on the status column of the monthly report next to overtime.

Originally Posted by jason.b75

Using the formula that I suggested to enter the 'Tardy' comment in the status column, you could use

=COUNTIF(December!I16:AC178,"tardy")

to count all instances of 'Tardy' on the sheet, but without some completed (fictional) data, names, etc on the sample, I don't know what will be going where to count occurrences by name.

Alright I've attached a sort of demo. Its for an electricians team and Each person is either a Material hander, Apprentice or Journeyman.
What I hope to achieve Is entering the most minimal amount of data wich will calculate\automate everything else. While also having a simplified summary version for the boss.
What Im trying to do is find some way to mark people as Apprentice or Journeyman so that their overtime\normal hours gets categories into the correct spot on Sheet: Hours and Expenses Table.
I have also attached an Attendance Record DEMO that I used for a very short period of time. The only problem with it is I cant calculate overtime and I cant put specific hours (for when they are tardy)
I may just be over thinking it - But any tips\tricks or suggestions would be greatly appreciated. Attendance Record DEMO.xls Employee Timecard DEMO.xlsx

Edit: I love how simple the Attendance Record DEMO is but again I cant put hours. I was brain storming and maybe I could make some sort of way that it will put a list of all Tardies on a seperate sheet maybe so when I put L I can put on that other sheet what exactly the time they came in at.