Last End time for that day formula

[Uzma Shaheen]

hHi All,

Hope you all are well - Im trying to add a formula that will give me the start time of a person and end time of a person for each day

These formulas seem to work fine for the start but falls over for the end shift. It seems to fall over when there is a lap of time.

see this example

[TABLE="width: 723"]
<tbody>[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]System Issues[/TD]
[TD="align: right"]12:33[/TD]
[TD="align: right"]13:20[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"]13:20[/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]Active[/TD]
[TD="align: right"]13:28[/TD]
[TD="align: right"]13:34[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Its showing 13:20 as end time which is incorrect - it should be 18:04 what i need is the correct start and end time

Here is the sample data

[TABLE="width: 659"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Id[/TD]
[TD]Name[/TD]
[TD]exception[/TD]
[TD]start[/TD]
[TD]stop[/TD]
[TD]StartShift[/TD]
[TD]EndShift[/TD]
[/TR]
[TR]
[TD="align: right"]04/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]NotReady_Default[/TD]
[TD="align: right"]11:14[/TD]
[TD="align: right"]15:52[/TD]
[TD="align: right"]11:14[/TD]
[TD="align: right"]15:52[/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]NotReady_Default[/TD]
[TD="align: right"]09:59[/TD]
[TD="align: right"]10:10[/TD]
[TD="align: right"]09:59[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]Coaching[/TD]
[TD="align: right"]10:10[/TD]
[TD="align: right"]12:17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]Active[/TD]
[TD="align: right"]12:17[/TD]
[TD="align: right"]12:23[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]Wrap[/TD]
[TD="align: right"]12:23[/TD]
[TD="align: right"]12:26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]Active[/TD]
[TD="align: right"]12:26[/TD]
[TD="align: right"]12:29[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]Wrap[/TD]
[TD="align: right"]12:29[/TD]
[TD="align: right"]12:33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]System Issues[/TD]
[TD="align: right"]12:33[/TD]
[TD="align: right"]13:20[/TD]
[TD][/TD]
[TD="align: right"]13:20[/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]Active[/TD]
[TD="align: right"]13:28[/TD]
[TD="align: right"]13:34[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]Wrap[/TD]
[TD="align: right"]13:34[/TD]
[TD="align: right"]13:37[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]System Issues[/TD]
[TD="align: right"]13:37[/TD]
[TD="align: right"]13:43[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]Active[/TD]
[TD="align: right"]13:43[/TD]
[TD="align: right"]17:57[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]Wrap[/TD]
[TD="align: right"]17:57[/TD]
[TD="align: right"]18:04[/TD]
[TD][/TD]
[TD="align: right"]18:04[/TD]
[/TR]
[TR]
[TD="align: right"]10/05/2018[/TD]
[TD="align: right"]1117779[/TD]
[TD]Name1[/TD]
[TD]NotReady_Default[/TD]
[TD="align: right"]09:59[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]09:59[/TD]
[TD="align: right"]10:00[/TD]
[/TR]
</tbody>[/TABLE]

The formula to get start shift is = IF(AND(C1=[@Name],A1=[@Date]),"",[@start])
Formula for end shift is =IF(AND([@Name]=C3,[@stop]=E3),"",[@stop])

Is there a better formula to get around this

Thank You

 

[Uzma Shaheen]

Apologies if im not clear in whats needed - please advise if there is more info needed

 

[Uzma Shaheen]

Pleaseeee anyone

would really appreciate your help