Creating automated timesheets

S

StacyH

I have an Excel spreadsheet designed to record and track information on
workers. It has 7 tabs for 7 days (Mon-Sun). Each of these 7 tabs is the
same. Each tab is organized by territories or markets of which there are 9,
and then by workers' name. Each row contains info on where each worker
worked that day, start/finish times, etc. (see Table 1).

TABLE 1

Monday 3/23/2009
# Driver Name Market Customer Start Time Finish Time
1 Worker 1 A ABC Company 5:00 AM 3:00 PM
2 Worker 2
3 Worker 3
4 Worker 4
5 Worker 5 A XYZ Company 4:30 PM 1:00 AM
6 Worker 6 A XYZ Company 3:30 PM 9:30 PM


Work varies greatly from week to week - workers can work any number of days
per week, and can work at different customers on different days. Info from
these 7 daily tabs is referenced into 9 new tabs, one for each market
[reference example: =IF(Mon!L$15="", "",Mon!L$15)]. This has the result of
organizing and presenting the data by market and by worker name, not by day.
Tables on these tabs show each worker’s work information for the week. All
info is automatically populated through references and formulas which
calculate hours worked (regular and OT) and totals. See Table 2 for a
partial example.

TABLE 2

Worker 1
Day Date Timesheet Customer Mkt Start Finish
M 23-Mar 123456 ABC Company A 5:00 AM 3:00 PM
T 24-Mar 123456 ABC Company A 5:00 AM 2:45 PM
W 25-Mar
Th 26-Mar 123999 DEF Company A 7:30 AM 5:30 PM
F 27-Mar
S 28-Mar
Su 29-Mar


It is from this point and the data available that I wish to create a
variation of Table 2: electronic time records or cards, one per worker for
EACH Customer he/she works for (see Tables 4a and 4b).

TABLE 4a
Worker 1
ABC Company
Day Date Timesheet Start Finish Total Hours Reg OT
M 23-Mar 123456 5:00 AM 3:00 PM 10 8 2
T 24-Mar 123456 5:00 AM 2:45 PM 9.75 8 1.75
W 25-Mar
Th 26-Mar
F 27-Mar
S 28-Mar
Su 29-Mar
Total Hours 19.75 16 3.75

TABLE 4b
Worker 1
DEF Company
Day Date Timesheet Start Finish Total Hours Reg OT
M 23-Mar
T 24-Mar
W 25-Mar
Th 26-Mar 123999 5:00 AM 2:45 PM 9.75 8 1.75
F 27-Mar
S 28-Mar
Su 29-Mar
Total Hours 9.75 8 1.75



Several elements would need to be addressed to efficiently move the data
from Table 2 into a workable time record:

1. There would need to be a SEPARATE electronic time record for each worker
representing the work he/she performed for EACH client.
2. A person could work at one customer for seven days which would result in
one time card
3. A person could work at seven different customers in seven days which
would result in seven time cards
4. The information would ideally automatically populate from Table 2 into a
printable timesheet

I have yet to be able to address these issues. I have used cell references
and formulas to create a table that totals the hours per customer and
includes all needed information. However it still shows multiple customers
in the same table. How do I get the info into separate tables? I would like
to utilize Excel, or possibly a mail merge with Word.

I know this is complicated, please feel free to email any questions to
(e-mail address removed) and I can get you more information if needed.
Thank you so much for your help.
 
S

StacyH

We have Excel 2003 here so I cannot open your link. I am not experienced in
Pivot Tables, and neither are the other people here who need to operate the
database, so I'd rather try something else. Any other ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top