Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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.

excelformulas-functions
MS Office Version: 2007
posted by: Sudhaa on Mon Feb 13, 2017 at 9:18 pm
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: 1989) 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

posted by: Sudhaa on Wed Feb 22, 2017 at 9:29 am
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.

posted by: ANAND KUMAR (rep: 35) on Thu Feb 23, 2017 at 1:41 am
Discuss


Answer the Question

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