index from the row sheet2 by match array, match array

0

The data is bio-punching time of employees for whole month.The data in sheet2,in Col_A emp ID for one month(nearly 50duplicates in and out punch) and datetime(21-10-2016 06:01:59) in col_B min 2-datetime for one date(in and out),I want to extract the min time for one date in C col and max time for the same date in D col from the same row where the ID in sheet1!$e$5, matches in the sheet2!A:A and the date in sheet1!B:B. can creat helper sheet to seperate date and time.

there is no title for any data it is imported or copy paste from *.dat file.

Answer
Discuss

Discussion

Please include a sample file that shows what you are trying to do. It will be a lot easier to give you a correct answer that way. (Edit your question to attach the sample file.)
don (rep: 1247) Feb 14, '17 at 5:04 am
Add to Discussion

Answers

0
Selected Answer

I found it by searching in the internet so many websites  for nearly 3 months and finally change one of the answer and chang it to my method of sheet. The below answer is based for my working criteria........

=IF(ISERROR(INDEX(PunchTime!$B$2:$D$5000,SMALL(IF(PunchTime!$B$2:$B$5000&PunchTime!$C$2:$C$5000=$P$4&B7,ROW(PunchTime!$B$2:$D$5000)),ROW($1:$1)),3)),TEXT(B8,"dddd"),INDEX(PunchTime!$B$2:$D$5000,SMALL(IF(PunchTime!$B$2:$B$5000&PunchTime!$C$2:$C$5000=$P$4&B7,ROW(PunchTime!$B$2:$B$5000)),ROW($1:$1)),3)) Ctrl+shift+Enter.

in the above can replace        TEXT(B8,"dddd")           as    ""       to recieve blank. and change SMALL to LARGE to achieve the maximum time as 17:32

I entered the above in B7  it result in time as 8:52 and so on my next problem is how to achieve 0 in col_L if  B is blank or Text like Sunday or Saturday 

Thank u for ur considering my presence

Discuss

Discussion

is it possible to simplify the above answer because I am not  an expert in Excel and just working in the excel from the 3 months and worked only for the above achievement.
Sudhaa Feb 22, '17 at 9:34 am
Add to Discussion
0

 As far as I could understood, You need MAX and MIN date against of employee ID If so then you may refer attachment.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login