Find in Exccel

Dinesh Bali
Dinesh Bali used Ask the Experts™
on
Hi,

Please help me in finding the finding first check in and last check out in excel for an employee.
File attached: AttendanceSheet.xlsx

Regards,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Here's one option. Create a unique list of employees and then use this array formula for the first entry:
=MIN(IF(B:B=J3,D:D))
To enter an array formula use Crtl+Shift+Enter. Change MIN to MAX for the last entry. Please refer to the attached, columns J:L. I used conditional formatting to blank out the two employees who had only one time entry.

Paul
AttendanceSheet122919.xlsx
Tom FarrarConsultant

Commented:
When you say "an employee" did you mean all employees?  For instance if you are looking for "an" employee are you looking for the who was there the longest, or... ?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Click the button in the attached workbook in which I added some new data for John.
29168201.xlsm
Rob HensonFinance Analyst

Commented:
I think you can get the result desired from a pivot table, with addition of a helper column combining the date and time.

See attached.
AttendanceSheet.xlsx

Author

Commented:
Many thanks all for your responses.

@Rob,
Pivot table is working for me. How do we get the time difference between check out and check in time of the employee in from of Pivot table.

@Flyster,
Many thanks for your solution. This is also good for me. In anyway, we can get the unique names and then apply your filters on it. I don't mind if we have to use VLOOKup

@Martin,
Many thanks to you as well. Its bit complex solution and also the excel is just highlighting the values. This needs lots of manual intervention to find details for all employees.

@All,
Thanks for your response. I want to find total in time of the employee in office, so I was looking for check in ad=nd check out time.

Please help me with the issues I commented. I don't mind if you need to use any other thing as well to identify the details like using VLookup, Pivot tables etc.

Many thanks again.
Rob HensonFinance Analyst

Commented:
You can get duration with a calculated item in the pivot, see attached.
AttendanceSheet.xlsx

Author

Commented:
Many thanks for this.
How you have added Duration field? I am using Excel 2013.

Below are my screenshot attached. Please advise.

Adding Calculated FieldError In Calculated Field
Regards,
Finance Analyst
Commented:
The duration was inserted as a calculated item rather than calculated field. If you put the cursor in the header row of the pivot table, the calculated item option is no longer greyed out. The calculated item options are shown below:
Calculated itemI used formula:
=IF(OR('Checked-In'=0,'Checked-In'="",'Checked-Out'=0,'Checked-Out'=""),0,'Checked-In'-'Checked-Out')

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial