Time logs

[jtilleyx]

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:

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

 

[jtilleyx]

Edit: one last piece to complicate it:
If the "in time" meets the lower boundary criteria, but the "out time" has not been entered then use upper boundary value as "out time" to calculate duration

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Time Tracking Log
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.