Timekeeping/Excel
Discussion
This is Excel and business related - so I tossed a coin and posted it here.
I'm trying to make the current timesheet (TS) procedure more efficient.
At present, each member of staff completed a TS and hands/e-mails it to a manager, the manager then manually inputs the details on a master TS to summarise everyones times. A separate master TS is then saved for each day.
I am trying to come up with a way where everyone completes their TS throughout the day in Excel, which at the end of the day would populate the master TS.
The issue is it cannot be done by a copy macro or cell linking (that I can think of). Reason being: if someone does not complete their TS before they leave, but does it the next day instead - it would cock the next day up.
I think I'm looking to be able to tell the master sheet to take a snapshot of all the individuals sheets at the click of a button i.e at the end of the day.
Does that make sense?
If it does and you can make any suggestions you'd make me a happy man!
Cheers
I'm trying to make the current timesheet (TS) procedure more efficient.
At present, each member of staff completed a TS and hands/e-mails it to a manager, the manager then manually inputs the details on a master TS to summarise everyones times. A separate master TS is then saved for each day.
I am trying to come up with a way where everyone completes their TS throughout the day in Excel, which at the end of the day would populate the master TS.
The issue is it cannot be done by a copy macro or cell linking (that I can think of). Reason being: if someone does not complete their TS before they leave, but does it the next day instead - it would cock the next day up.
I think I'm looking to be able to tell the master sheet to take a snapshot of all the individuals sheets at the click of a button i.e at the end of the day.
Does that make sense?
If it does and you can make any suggestions you'd make me a happy man!
Cheers
Looks like huge potential for things to get screwed up easily - what with multiple files, sheets, and/or users.
Depending on the numbers of staff you're talking about I might be tempted to take a baby step first by developing a standardised sheet for employees that they then email to manager who then copies the sheets into his master file that then rolls the data up to the required level.
If you name/code each employee spreadsheet tab in some way, you could automate the roll up very easily indeed.
Do wonder though if employees will welcome it being electronic though - more files open on PC and probably less chance they'll note things down as they go through the day?
Depending on the numbers of staff you're talking about I might be tempted to take a baby step first by developing a standardised sheet for employees that they then email to manager who then copies the sheets into his master file that then rolls the data up to the required level.
If you name/code each employee spreadsheet tab in some way, you could automate the roll up very easily indeed.
Do wonder though if employees will welcome it being electronic though - more files open on PC and probably less chance they'll note things down as they go through the day?
There are many open source, and even more commercial, software packages available to do this through a simple web browser.
If you don't like that, a Google Form would do it for you, and drop the results straight into a Google Docs Spreadsheet, which could then be opened by the manager in Excel.
Is there any reason specifically to use Excel through the whole process? If you need offline access, it is easy to enough to produce emailable forms.
If you must do it with Excel, you can easily use a master / slave sheet scheme. The employees have the 'slave' sheets, and email them back to the manager, who can merge them into the master sheet. If it is one column per day, say, it doesn't matter if the sheets are submitted late. But is still a manual process and IMHO a needless one.
I used to have 100+ temps out working per day, each filling-in manual timesheets. I gradually transitioned many of them (where clients allowed web access) over to online timesheets, which worked very well.
The only question I would have in your circumstance - other than the foregoing - is why save a spreadsheet for every day? Does that go into some form of automated workflow system? Why not store all the hours in a database somewhere (or just a large spreadsheet), and then generate a daily report for anybody who needs it? This way you have all the hours data in a single place for reference, which is a big help.
If you don't like that, a Google Form would do it for you, and drop the results straight into a Google Docs Spreadsheet, which could then be opened by the manager in Excel.
Is there any reason specifically to use Excel through the whole process? If you need offline access, it is easy to enough to produce emailable forms.
If you must do it with Excel, you can easily use a master / slave sheet scheme. The employees have the 'slave' sheets, and email them back to the manager, who can merge them into the master sheet. If it is one column per day, say, it doesn't matter if the sheets are submitted late. But is still a manual process and IMHO a needless one.
I used to have 100+ temps out working per day, each filling-in manual timesheets. I gradually transitioned many of them (where clients allowed web access) over to online timesheets, which worked very well.
The only question I would have in your circumstance - other than the foregoing - is why save a spreadsheet for every day? Does that go into some form of automated workflow system? Why not store all the hours in a database somewhere (or just a large spreadsheet), and then generate a daily report for anybody who needs it? This way you have all the hours data in a single place for reference, which is a big help.
I'm not sure how you could use Excel itself to produce an automated solution. As mentioned above, if you were looking to invest in a more flexible time recording system there are some great products out there. We sell some from a company called Chipdrive:
http://www.thebarcodewarehouse.co.uk/id-card-print...
http://www.thebarcodewarehouse.co.uk/id-card-print...
As before and before, Excel does not sound like the best tool for this job.
Sticking with Excel, I would have the employees upload timesheets to their own folders on a server and give the manager a macro that pulls details out of the employee timesheets. That way, the Manager can click his button to refresh the data if someone has updated their timesheet.
Sticking with Excel, I would have the employees upload timesheets to their own folders on a server and give the manager a macro that pulls details out of the employee timesheets. That way, the Manager can click his button to refresh the data if someone has updated their timesheet.
Must you have individual time sheets? Can't tell from the info provided or your profile what you do and why you need to measure each individual's contribution by time (and not output). There are very few progressive industries/sectors left where this is absolutely necessary to add true value. I would be genuinely interested to know and to also know if this has been reviewed in terms of a value chain analysis, including what it 'costs' to record everyone's time put in and how you exploit that data, other than to pay people, to improve value and thus, again, outputs?
A good free time tracker application can be found on the asp.net
http://www.asp.net/downloads/starter-kits/time-tra...
http://www.asp.net/downloads/starter-kits/time-tra...
I dug this topic up from google, and wondered if anyone had come across anything free or recommended for employee attendance? Just need to know if employee is In, offsick, vacation etc and number of days per month etc. Currently we use excel sheets, that are scanned/mailed then manually inputted at the other end...
Gassing Station | Business | Top of Page | What's New | My Stuff