Separate hours worked from hours on break, lunch, etc.?

[Lostguy]

Hello!
I was looking for a program to schedule employees that showed when
they work, are on break, lunch, etc so I can see conflicts and make
sure the required number of people are in each work area. Downloaded
many, but they either cost too much for us, or were not as
customizable as we needed.

So I am trying the Excel route.

Here's the setup:

New worksheet.
Merge A1:A4 and put an employee name ("Bill")
In B1, text "Arrival"
Merge B2:B4 and put time (07:30)
In C1, dropdown list of the four work areas and other off-work places
("Reception, Validator, Interviewer, Quality Check, OUT (LUNCH), OUT
(SICK), OUT (BRK), OUT (ERRAND), OUT (HOLIDAY), OUT (TRNG)")
IN C2, formula =IF(B2="","",B2) (07:30)
IN C3, time ("09:15")
IN C4, formula =IF(C3>C2,C3-C2,"")
Copy C1:C4 to D14.
Change the D1 formula to =IF(C3="","",C3)
Copy D14 all the way to P14.
In Q1, text "DEPART"
Merge Q2:Q4 and put formula =MAX(C23)

Copy A1:Q4 down for as many employees as we are tracking. This
spreadsheet is supposed to track when the employee shows up, when they
work and where and how long, and every break/non-working period.

Now for the questions.

In R1. I have "Total Hours Here"
IN R2, formula =q2-b2
IN R3, I have "Actual Work Hours"
IN R4, I need a formula that captures this:

a) If the Row 1 value is either Reception, Validator, Interviewer, or
Quality Check, then sum the Row 4 value for that column."

b) Is there a better way to set this up that someone already knows
about? One employee could work Reception for an hour, then Validator
for 15 minutes, take a break, work Reception again for 20 minutes,
take another break, work Quality Check, take another break, etc. so
the schedule for this person has to have many, many columns to track
each of the possible starts/stops. Another employee can just come to
work, go to lunch, come back to work and leave, so they will only have
3 columns of location data. I am preparing for the worst case, but I
could either have way too many columns than I actually need, or not
enough columns in the case of trhe extremely mobile employee.


Alot of words, I know. I appreciate the help!

VR/

 

[AltaEgo]

Spreadsheet design is easier if you aim to get field names at the top of the
data columns (not necessarily in row 1 of the worksheet) and records
entered in rows below. If you find yourself entering record data across a
spreadsheet instead of down, you can usually be sure you need to go back and
re-consider your layout.

The alternative design below will allow the addition of many changes of task
each day. Changes of employee are easy to accommodate Just add or delete the
name from a employee name list.

A) lookup table Columns (say) A-B

- Reasons (Column A): Validator, Interviewer... etc.
- For each reason (column B), enter Yes/No depending whether you want to
count it as work time or down time.

B) An employee name lookup list

C) Sets of six columns:

Headings are:
- EmployeeName
- Reason: Use data validation lookup list of reasons (from column from A)
- Start Time: manual
- End time: manual
- TaskTime: EndTime-StartTime
- WorktimeY/N : Lookup value in column B based on Reason selected

D) Data filter will allow you to look at records for an individual record
when you need to add a new record

E) You should be able to perform required calculations with sumif() and
pivot tables.