Return the end time for a person ba looking at the start time for another task

[Tempt]

Hi, everyone.

I have a table of people with a date, start time and end time, as well as task performed by that person. The problem is that most cells in the colum of end time are blank and I would like to take the start time of that person on the same day for another task (the person changed the task but didn't enter the end time).

Now I am trying to figure out how to return the end time for the same person on the same date for a different task when he/she changes it where I define the end time as the start time for the same day and person but a different task.

My table looks as follows.

What I want is a formula in column F to return the end time defined as a start time for a different task for the same person on the same day (here the value in cell E3). Alternatively I could do it in another sheet where I could match the corresponding value, which I also can't figure out.

Any help and tip would be much appreciated.

 

[Toadstool]

Hi Tempt,

Can I assume the data is in ascending date/time sequence?

 

[Tempt]

Hi, Toadstool.

Yes, the data is in an ascending order (date and time).

 

[Toadstool]

As some End Times are entered then you'll need an extra column for the Calculate End Time, being the entered time or next for that day for that worker.

If you've no following time for an entry then I show "No end time" though you may want to force it to normal clocking off time.

Tempt.xlsx
	A	B	C	D	E	F	G	H
1	Abbreviation	Task	Worker	Date	Start Time	End Time	Calculated End Time	Hours Worked
2	MIS	Picking	Worker1	03-Jan-21	9:30		10:30	1:00
3	MIS	Packing	Worker1	03-Jan-21	10:30		11:30	1:00
4	TTE	Parking	Worker1	03-Jan-21	11:30		12:30	1:00
5	JKL	Porting	Worker1	03-Jan-21	12:30		No end time
6	MIS	Picking	Worker5	03-Jan-21	9:30		10:30	1:00
7	TTE	Packing	Worker5	03-Jan-21	10:30	16:45	16:45	6:15
8	JKL	Parking	Worker5	04-Jan-21	11:44		12:30	0:46
9	MIS	Porting	Worker5	04-Jan-21	12:30		No end time
10	TTE	Picking	Worker1	04-Jan-21	9:30		10:30	1:00
11	JKL	Packing	Worker1	04-Jan-21	10:30		11:30	1:00
12	MIS	Parking	Worker1	04-Jan-21	11:30		12:30	1:00
13	TTE	Porting	Worker1	04-Jan-21	12:30	17:00	17:00	4:30
Sheet1

Cell Formulas
Range	Formula
G2:G13	G2	=IF(F2="",IFERROR(INDEX($E$2:$E$9995,AGGREGATE(15,6,ROW($E$2:$E$9995)-ROW($E$1)/(($C$2:$C$9995=C2)*($D$2:$D$9995=D2)*(ROW($E$2:$E$9995)>ROW(E2))),1)),"No end time"),F2)
H2:H13	H2	=IFERROR(G2-E2,"")

 

[Tempt]

As some End Times are entered then you'll need an extra column for the Calculate End Time, being the entered time or next for that day for that worker.

If you've no following time for an entry then I show "No end time" though you may want to force it to normal clocking off time.

Tempt.xlsx
	A	B	C	D	E	F	G	H
1	Abbreviation	Task	Worker	Date	Start Time	End Time	Calculated End Time	Hours Worked
2	MIS	Picking	Worker1	03-Jan-21	9:30		10:30	1:00
3	MIS	Packing	Worker1	03-Jan-21	10:30		11:30	1:00
4	TTE	Parking	Worker1	03-Jan-21	11:30		12:30	1:00
5	JKL	Porting	Worker1	03-Jan-21	12:30		No end time
6	MIS	Picking	Worker5	03-Jan-21	9:30		10:30	1:00
7	TTE	Packing	Worker5	03-Jan-21	10:30	16:45	16:45	6:15
8	JKL	Parking	Worker5	04-Jan-21	11:44		12:30	0:46
9	MIS	Porting	Worker5	04-Jan-21	12:30		No end time
10	TTE	Picking	Worker1	04-Jan-21	9:30		10:30	1:00
11	JKL	Packing	Worker1	04-Jan-21	10:30		11:30	1:00
12	MIS	Parking	Worker1	04-Jan-21	11:30		12:30	1:00
13	TTE	Porting	Worker1	04-Jan-21	12:30	17:00	17:00	4:30
Sheet1

Cell Formulas
Range	Formula
G2:G13	G2	=IF(F2="",IFERROR(INDEX($E$2:$E$9995,AGGREGATE(15,6,ROW($E$2:$E$9995)-ROW($E$1)/(($C$2:$C$9995=C2)*($D$2:$D$9995=D2)*(ROW($E$2:$E$9995)>ROW(E2))),1)),"No end time"),F2)
H2:H13	H2	=IFERROR(G2-E2,"")

Hey, thanks for the answer. Could you please explain what the formula does?

 

[Toadstool]

If and End Time was entered then use it.
=IF(F2="",...F2)

If the AGGREGATE fails (it gives a #NUM error if no cases are found) then no matching next job was found.
IFERROR(...,"No end time")

Retrieve the Start Time
INDEX($E$2:$E$9995,...)

AGGREGATE is using the SMALL function (15) and ignoring internal errors. The ROW means it's not looking into each cell of $E$2:$E$9995 but rather looking at every row from 2 to 9995 so we must subtract the heading row to get the actual row number.
The 1 at the end is because it can get the first, second, third, etc. but in this case we only want the first, so 1.
AGGREGATE(15,6,ROW($E$2:$E$9995)-ROW($E$1)...,1)

Now for every row number we divide it by the logicalresult of three comparisons. If any is FALSE (zero) then multiplied together will always be zero and divided into the row number will give #DIV/0 which the option 6 of AGGREGATE ignores.
/

So for each row of C, D and E 2 to 9995 it checks if they equal the current C or D (if so then we have the same Worker and Date) and if the row being looked at is greater than the current row (so we don't get an earlier time or time of the current row).
(($C$2:$C$9995=C2)*($D$2:$D$9995=D2)*(ROW($E$2:$E$9995)>ROW(E2)))

 

[Tempt]

If and End Time was entered then use it.
=IF(F2="",...F2)

If the AGGREGATE fails (it gives a #NUM error if no cases are found) then no matching next job was found.
IFERROR(...,"No end time")

Retrieve the Start Time
INDEX($E$2:$E$9995,...)

AGGREGATE is using the SMALL function (15) and ignoring internal errors. The ROW means it's not looking into each cell of $E$2:$E$9995 but rather looking at every row from 2 to 9995 so we must subtract the heading row to get the actual row number.
The 1 at the end is because it can get the first, second, third, etc. but in this case we only want the first, so 1.
AGGREGATE(15,6,ROW($E$2:$E$9995)-ROW($E$1)...,1)

Now for every row number we divide it by the logicalresult of three comparisons. If any is FALSE (zero) then multiplied together will always be zero and divided into the row number will give #DIV/0 which the option 6 of AGGREGATE ignores.
/

So for each row of C, D and E 2 to 9995 it checks if they equal the current C or D (if so then we have the same Worker and Date) and if the row being looked at is greater than the current row (so we don't get an earlier time or time of the current row).
(($C$2:$C$9995=C2)*($D$2:$D$9995=D2)*(ROW($E$2:$E$9995)>ROW(E2)))

Hey, thank you very much. It works.