Formula to find first entry for every person for each day

[darylexcel]

Dear all,

Is there a formula that I can retrieve the first door log time for every person for every day?

Will the Index and Match work?

Sample data is below

Column A Column B Column C
Person A 18 May 8.30am
Person A 18 May 12.30pm
Person A 19 May 9.30am
Person A 19 May 14.40pm
Person A 19 May 17:30pm
Person A 20 May 8.45am
Person A 20 May 17.30pm
Person B 18 May 8:00am
Person B 18 May 10.00am
Person B 18 May 11.00am
Person B 18 May 13.30pm
Person B 18 May 17.40pm
Person B 19 May 8.30am
Person B 19 May 14.40pm
Person B 19 May 17:30pm
Person C 18 May 9.10am
Person C 18 May 17.45pm
Person C 20 May 8.45am
Person C 20 May 11.30am
Person C 20 May 12.30pm
Person C 20 May 14.30pm
Person C 20 May 17.30pm

So the results will be:

18 May Person A 8.30am
18 May Person B 8.00am
18 May Person C 9.10am
19 May Person A 9.30am
19 May Person B 14.40pm
19 May Person C No Record
20 May Person A 8.45am
20 May Person B No Record
20 May Person C 8.45am


Thanks!

[Glenn Kennedy]

We can't see what values are in which cell. Please attach an Excel workbook!!!

[Richard Buttrey]

Hi,

Are you able to change your data so that you capture a proper time or time number rather than text like "8.45am" i.e. 0.364583333333333 ?

[darylexcel]

Dear Glenn,

This is the excel workbook which I entered the sample data.

Thanks.

Daryl

[darylexcel]

Dear Richard,

I don't think I can convert. The original time from the door access software after exported to excel actually appears as hh:mm:ss format e.g. 09:00:11

For the sample data, I am just giving an example. Only wish to extract the first log for each day for every person.

Thanks!

Daryl

[Glenn Kennedy]

It doesn't seem to mattter too much, as the times are in chronological order, so an INDEX MATCH, checking 2 criteria, will stop at the first match.

[Pete_UK]

You could set up a simple table with names in column E (starting from E2) and dates in row 1 (starting from F1), and this formula in F2:

=IFERROR(INDEX($C$2:$C$23,MATCH(1,INDEX(($A$2:$A$23=F$1)*($B$2:$B$23=$E2),0),0)),"")

Copy across and down, as the attached file shows.

Hope this helps.

Pete

[Richard Buttrey]

Dear Richard,

I don't think I can convert. The original time from the door access software after exported to excel actually appears as hh:mm:ss format e.g. 09:00:11

For the sample data, I am just giving an example. Only wish to extract the first log for each day for every person.

Thanks!

Daryl

Hello Daryl,

Please note that when you give examples you should show examples that accurately reflect the ACTUAL data you are working with, otherwise we all waste our time considering factors that aren't true or relevant. Please note that there is a big difference as far as Excel is concerned between '8.30am' and .354167 which is the time number for 08:30

[darylexcel]

Thanks Pete and Glen for the solving formulas.

Yes Richard, will take note of this.

Grateful to all of you...

Daryl

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.