• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Time Reporting


New Member
Ok, so I administrate a database that is set up for time reporting. However, it gets very poor support for, of all things, political reasons. I am not even close to having sufficient mastery over Access to create a better database. However, I am pretty good with excel. I thought I might list out my requirements and see if someone could think of a good way to set this up.

Employees have 2 attributes, a name, and a username. Employees are also a member of a team, but they do not have to be associated with the team in any formal sense.

Teams have accounts. These accounts are what the employees report their hours to. There are about 12 different teams and 300 some accounts. Some very seldom see time reported to them.

Each week the employees would have to go into the sheet and report their time. In order to encourage timely reporting, I want them to do it on a weekly basis. They should report at least 40hours each week, if they are on vacation, or sick they should still report.

It seems like it would be pretty easy to make a sheet that had drop downs for all the employee names in one column, authenticated by a list kept in a hidden tab. Additionally, I could divvy up account options by team. However, I'm not sure how I would have them pick team X and then have a drop down of all the accounts for team X.

After selecting their name, their team, and their account, Then there would just be 4 or 5 columns each month depending on how long the fiscal month is for them to report time to. I would need to send an email to all of the individuals who have forgotten to report for a given week, which could be pretty easily generated with some quick sorting each week.

However, here is the rub there are about 200 employees. There will often be multiple people that need the sheet at the same time. I have heard that there is a lot of difficulty with setting up shared workbooks, but it seems like, especially if I lock the whole sheet down, except for where employees report time, there is no way it could get messed up given the simplicity of the thing.

Suggestions? ideas? Sympathy for someone who can't set up access to do what he needs? Thank you in advance for taking the time to read my post, fellow excel nerds.
Hi Garetjax,

As I understand, after reading your post, that there are mainly tthree things:

a) Creation and maintenance of time tracking spreadsheet for around 200 staff members;

Possible Solution: As you mentioned, that staff are divided between 12 functional teams. In my limited experience, I would suggest to create either 12 separate workbooks (one for each group). All these 12 workbooks should contain single index sheet, where team members (belonging to that team)select their name and input their password to go to their specific sheet for logging their hours.

If maintaining 12 separate workbooks is cumbersome (which I do not blame you) see if there are any corelations between any two teams and create a one workbook for two teams.

In order to create a DEPENDENT drop down list there's a very useful and easy to understand procedure available at CONTEXTURES, Debrah Dalgelish is the owner of the website and I have learned quite a few tricks from her website.

b) Your second issue is how to inform staff to log in their hours.

Possible Solution: There's an Excel addin available over the internet that creates a send e-mails through Excel. All you have to do is to create certain trigger points in staff sheet when they should get a reminder. Unfortunately, at this time I do not remember the name of this addin but I am sure if you GOOGLE it you will found it. And I believe it is also free.

c) The last part: Let's say you do ended up creating and maintaining 12 workbooks (one for each team).

Possible Solution: It may very well be a night mare to checking each of these 12 workbooks. One possible solution is to create a master (or controlling) workbook, This master workbook is linked with each of the 12 team workbooks and can show the following information (for example):

1) A weekly summary for each team (like how many hours worked or any other KPI)

2) It can also show how many and also perhaps the staff names who have not submitted their hourly info.

I know the above is lot to digest, but try to read it in bits and pieces and then see how the whole picture is formulated.