CODEWITHSUNDEEP
excelexcel-formula
Rookie
Rookie

Reputation: 140

Find earliest entry in a table

Consider the following table of employees.

employees

It's fairly simple right? Clock in when you start, clock out for lunch, clock back in after lunch, clock out when you leave. But can someone please tell me what time these employees actually started? So for example how would you go about saying Tom was here at 6:51 AM and not at 12:23 PM?

For more reference, all entries look like this. They have at least 4 entries, (some one, no lunch).

How do I go about writing a formula that returns the start time?

I edited the table and added a helper column for the date. See below.

ANSWER:

=AGGREGATE(15,6, $C$2:$C$7/((A11=$A$2:$A$7)*(B11=$B$2:$B$7)),1)

with date

Upvotes: 0

Views: 107

Answers (2)

basic
basic

Reputation: 11968

If the working hours are within the day, you can search for the smallest time. If there is a night shift over midnight, then there is a problem ...

=AGGREGATE(15,6,(1/($A$2:$A$5=A2)*(INT(B2)=INT($B$2:$B$5)))*($B$2:$B$5),1)

enter image description here

Upvotes: 1

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2639

I've written down this formula for range E2:

=IF(AND(A2=A3,TRUNC(B2,0)-TRUNC(B3,0)=0),MIN(B2,B3),IF(AND(IFERROR(TRUNC(B2)-TRUNC(B1)=0,FALSE),E1=MIN(B2,B1)),"",B2))"

Since english is not my default language on Excel, i've just manually translated it. Anyway, just in case, here is the VBA code to paste it in range E2:

Range("E2").FormulaR1C1 = "=IF(AND(RC[-4]=R[1]C[-4],TRUNC(RC[-3],0)-TRUNC(R[1]C[-3],0)=0),MIN(RC[-3],R[1]C[-3]),IF(AND(IFERROR(TRUNC(RC[-3])-TRUNC(R[-1]C[-3])=0,FALSE),R[-1]C=MIN(RC[-3],R[-1]C[-3])),"""",RC[-3]))"

I've tried various configurations and it works for me. Only point: list must be sorted by Name and Clock In.

Upvotes: 0

Related Questions