Help needed created a complex (in my opinion) timesheet

Hello all,

I have just joined this forum after spending 2 days on Google and YouTube trying to figure out how to solve a problem myself, and coming up short. Any help would be much appreciated.

The back-story is that my dad had a major operation and as he can't go to work for some time he's been asked to produce some work from home, which he's asked me to help him with. I'm no expert at Excel but I've educated myself about the basics with YouTube tutorials, and have managed to get some formulas working, but have no idea how to come up with a couple that have left me stumped.

The time sheet is not a simple log in, log out, subtract lunch hour one - if it was I think I'd just about be able to manage it. Instead, it needs to consist of multiple jobs on a single machine throughout the day. He works in a print factory so a machine can have anywhere from 1-50 jobs per day. This is where I'm getting stuck - figuring out how to make a table that allows such a huge level of variance, and still works out a formula for however many jobs are put in. The aim of the timesheet is to compare the output of different workers to one another, to check how much time is being "wasted" between jobs, to compare expected output with actual output, as well as to easily see how many items have been produced per hour, per day, per week etc.

The subjects that I want are:

Name (of person running the machine), Machine Name, Clock In (for multiple jobs), Clock Out (for multiple jobs), Total

I also want other things such as quantity produced, but I think once I can figure this bit out myself.

I have seen a tutorial explaining how to subtract one time from another to arrive at a total, however I can't find anything that tells you how to do it for multiple times within one day. For instance, if someone called John logs in for the first job at 6:00am and logs out at 06:45, then logs in at 06:51 and logs out at 07:30, then logs in at 07:40 and logs out at 08:00, I want it to calculate that the total time was 1hr 44 mins or 104 mins, and I also want it to calculate the time "wasted" in between jobs which would be 16 minutes. The more that are added, eg 20 or 30 jobs, the more complicated it becomes. I thought about laying it out like this:

Name, Machine, Total, Clock In, Clock Out, Clock In, Clock out, Clock In, Clock Out etc etc

With 50 "clock in" and "clock out"s, but besides looking terrible any subsequent formula would be messed up by all of the empty slots.

I realise this is a whole lot of information and I have no idea if I've explained myself clearly enough, but if anyone can help I would be very thankful, and I hope nobody thinks I'm just being lazy about it - I have tried for a couple of days now.

Thanks in advance,

Rick

This might be helpful.
You will have to enter time worked as decimals not time in time out.
Sample data has been entered just to show some of the capabilities.
It is a weekly timesheet and macros must be enebled to function correctly.
Several reports are automatically generated.

ncmay

Hi Rick,

Just a couple of questions so I can get my head around what you need.

Do you need man-machine information - i.e. John worked on Machine 1 for a total of 3:20, on Machine 2 for a total of 1:40, and on Machine 3 for a total of 0:45?

Do you calculate "wasted time" on a machine basis? i.e. 0:20 wasted on Machine 1 and 0:15 wasted on Machine 2? When John "wastes" time between finishing a job on Machine 1 and starting a job on Machine 2, to which machine is the wasted time allocated?

Are there many man-machine combinations? E.g. four men and three machines, or twelve men and ten machines? A large number of combinations might mean that the layout of the worksheet becomes complicated.

Given that there are many input variables, and that we have only a general idea of how you want to present the results, it would be quite easy for someone to spend quite a bit of time constructing an application only to discover that it wasn't quite what you wanted.

Regards,

Greg M

Thanks for all the replies so far, helpful stuff.

newdoverman - I didn't mean to suggest I'm mixing it in with payrolls, it's my fault for using the word timesheet which was misleading. It's purely job time analysis as you put it, describes my aim much more accurately. If I did it as one row per job, and the number of jobs varied each day, my concern is that every time a new row is created it will be blank and all the formulas in place would need to be adapted each time a row is added. For example, if the formula at the end is put in place to work out the average of 3 rows ie 3 jobs, but then it turns out there is a 4th job, will the formula need to be rewritten? Sorry if this is unclear.

ncmay - Thanks very much. I have downloaded the timesheet and am looking through it now. I can see signs of what I want in there but not sure if it's fully adaptable, but will spent some time working with it and see if I can alter it accordingly.

Greg M - sorry for not laying everything out properly, was throwing a lot of info out there. In response to your questions:

- No I don't need man-machine information. Every employee specialises in one machine, so there will not be a crossover eg John worked on machine 1 then machine 2. John will only ever work on machine 1. However, there will be more than one employee able to work on any machine, usually in shifts of 6am-2pm and then 2pm-10pm eg John worked on machine 1 from 6am-2pm then Mary worked on machine 1 from 2pm-10pm. The aim is to calculate their output during their shifts and be able to compare them, as in theory their output should be very similar, but often isn't.

- "Wasted time"...there are 2 main times that I mean when I use that term. The first is any time from the start of the shift (at 6am) until the beginning of the first job. Eg John comes in has a coffee, and turns the machine on at 6:04am. I want the spreadsheet to calculate that there are 4 minutes lost when typing in his start time. Then the 2nd main time where there's "wasted time" is at the switch over from morning shift to afternoon staff at 2pm. Eg John stops the machine at 1:59pm and Mary starts it at 2:02pm, so I want the spreadsheet to show 3 wasted minutes. I can then add a simple formula to add these 2 together to show the wasted minutes in that day and then add the daily totals from a week to see how many hours are wasted between jobs per week.

- The man-machine combination is quite straightforward, it's one man on a machine at any given time, and usually 2 people per machine per day (one after the other as stated above), although sometimes a 3rd person comes in, again taking over from the 2nd. There is no crossover period. However, there can be anything from 1-50 jobs per machine per day, which each need to be recorded individually.

- I should also point out, in relation to the "wasted time", that the jobs are broken down into "time spent preparing" and "time spent running machine", which need to be calculated individually. For instance if John is producing 2000 copies more than Mary when they've both done an 8 hour shift there has to be a reason such as the fact that Mary is taking much longer to prepare the job than John, which would be discovered on this spreadsheet.So for every job on every machine (let's say there are 20 jobs on Machine 1) there has to be a recording of preparation time and a recording of the time the machine was running. It's a printing firm, by the way, so it will be recording things like books produced etc.

I hope this information helps everyone understand a bit better, and I already appreciate the help. Thanks.