Looking for some help on best approach:
I need to track how labor is allocated during an overnight shift 17:00 to 01:30 with a 30 minute break at 22:00. The labor can be assigned to 1 of 2 clients (Hugo & Morphe) at any given time. After assigned a client, labor can be assigned 1 ot 2 tasks (Pick or Pack) at any time.
I would like to create a simple log that can be completed each time an assignment is changed. Something like below:
Each hour, on the hour, I need to be able to see how much time was spent in each task for each client
EX1: How many hours were used from 7PM-8PM PACKING HUGO
EX2: How many hours were used from 8PM-9PM PICKING MORPHE
Mini sheet example data
I need to track how labor is allocated during an overnight shift 17:00 to 01:30 with a 30 minute break at 22:00. The labor can be assigned to 1 of 2 clients (Hugo & Morphe) at any given time. After assigned a client, labor can be assigned 1 ot 2 tasks (Pick or Pack) at any time.
I would like to create a simple log that can be completed each time an assignment is changed. Something like below:
Name | Client | Task | Time In | Time Out | Lunch Y/N |
Person 1 | Hugo | Pick | 17:00 | 22:30 | Y |
Person 2 | Hugo | Pack | 17:00 | 22:00 | N |
Person 3 | Morphe | Pack | 17:00 | 1:30 | Y |
Person 1 | Morphe | Pack | 22:30 | 1:00 | N |
Person 2 | Hugo | Pack | 22:15 | 1:30 | Y |
Person 1 | Morphe | Train | 1:00 | 1:30 | N |
Each hour, on the hour, I need to be able to see how much time was spent in each task for each client
EX1: How many hours were used from 7PM-8PM PACKING HUGO
EX2: How many hours were used from 8PM-9PM PICKING MORPHE
Mini sheet example data
Labor Tracker Template.xlsx | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
1 | Name | Client | Task | Time In | Time Out | Lunch Y/N | Note 1 | Need to be able to see how many hours worked between a specified time period filtered by client and task | |||||||||||||
2 | Person 1 | Hugo | Pick | 17:00 | 22:30 | Y | |||||||||||||||
3 | Person 2 | Hugo | Pack | 17:00 | 22:00 | N | |||||||||||||||
4 | Person 3 | Morphe | Pack | 17:00 | 1:30 | Y | Note 2 | If the time period worked spans 22:00-22:30 deduct 30 minutes | |||||||||||||
5 | Person 1 | Morphe | Pack | 22:30 | 1:00 | N | |||||||||||||||
6 | Person 2 | Hugo | Pack | 22:15 | 1:30 | Y | |||||||||||||||
7 | Person 1 | Morphe | Train | 1:00 | 1:30 | N | Example One | Time | 17:00-18:00 | 0 | line(s) 2, 3, & 4 meet this criteria | ||||||||||
8 | Client | Morphe | only line(s) 4 meets the criteria | ||||||||||||||||||
9 | Task | Pick | line 4 does not meet this criteria - final result = 0 | ||||||||||||||||||
10 | |||||||||||||||||||||
11 | Example One | Time | 22:00-23:00 | 0.75 | line(s) 5 & 6 meet this criteria | ||||||||||||||||
12 | Client | Hugo | only line(s) 6 meets the criteria | ||||||||||||||||||
13 | Task | Pack | line 6 worked 45 minutes between 22:00-23:00 - final result = 0.75 | ||||||||||||||||||
Time Log |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
A2:A7 | List | =Data!$A$2:$A$4 |
B2:B7 | List | =Data!$B$2:$B$3 |
C2:C7 | List | =Data!$C$2:$C$4 |
K7 | List | =Data!$F$2:$F$10 |
K8 | List | =Data!$B$2:$B$3 |
K9 | List | =Data!$C$2:$C$4 |
K11 | List | =Data!$F$2:$F$10 |
K12 | List | =Data!$B$2:$B$3 |
K13 | List | =Data!$C$2:$C$4 |