Finding sign in sign out time for employee

Nihasan

New Member
Joined
Dec 15, 2020
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello,

I have a sheet with columns: Date , Sign in, Sign out, Emp ID and another sheet with Dates and Emp ID.
Employee IDDateTimeEventSign inSign out
02020-12-0413:50:08Exit13:50:08
02020-12-0413:52:25Exit13:52:25
7011242020-11-3013:31:30Entry13:31:30
7011242020-11-3021:21:12Exit21:21:12
7011242020-12-0113:29:16Entry13:29:16
7011242020-12-0213:14:24Entry13:14:24
7011242020-12-0313:35:28Entry13:35:28



I want to have the sign in and sign out times if the dates and Emp IDs match on both the sheets.(
DateSign InSign outEmp. #
10/25/20718025
10/25/20702195
10/25/20702800
10/23/20707349
10/25/20731102
10/25/20732603
10/26/20712931
10/27/20708649
10/25/20701124
)

I have attached a sample sheet below for reference.

I want to populate the sign in -sign out times for Emp IDs if the dates match.

Thanks in advance
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Samples where there are some more matching entries would help & also the expected results entered. The best way to give both is with XL2BB

Anyway, see if something like this could work for you. I have altered the sample data to get a few matches for what I think you are trying to do.

Nihasan.xlsm
ABCDEF
1Employee IDDateTimeEventSign inSign out
271802525-Oct-2013:50:08Entry10:48:1513:50:08
371802525-Oct-2013:52:25Exit13:52:25
470112430-Nov-2013:31:30Entry13:31:30
570280030-Nov-2021:21:12Exit21:21:12
670112401-Dec-2013:29:16Entry13:29:16
770112402-Dec-2013:14:24Entry13:14:24
870112403-Dec-2013:35:28Entry13:35:28
Sheet1


Nihasan.xlsm
ABCD
1DateSign InSign outEmp. #
225-Oct-2010:48:1513:50:08718025
329-Nov-20  702800
430-Nov-20 21:21:12702800
527-Oct-20  708649
601-Dec-2013:29:16 701124
702-Dec-2013:14:24 701124
Sheet2
Cell Formulas
RangeFormula
B2:C7B2=IFERROR(IF(INDEX(Sheet1!E:E,AGGREGATE(15,6,ROW(Sheet1!E$2:E$8)/((Sheet1!$A$2:$A$8=$D2)*(Sheet1!$B$2:$B$8=$A2)),1))=0,"",INDEX(Sheet1!E:E,AGGREGATE(15,6,ROW(Sheet1!E$2:E$8)/((Sheet1!$A$2:$A$8=$D2)*(Sheet1!$B$2:$B$8=$A2)),1))),"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
This is a little shorter. :)

Nihasan.xlsm
ABCD
1DateSign InSign outEmp. #
225-Oct-2010:48:1513:50:08718025
329-Nov-20  702800
430-Nov-20 21:21:12702800
527-Oct-20  708649
601-Dec-2013:29:16 701124
702-Dec-2013:14:24 701124
Sheet2
Cell Formulas
RangeFormula
B2:C7B2=IF(COUNTIFS(Sheet1!$A$2:$A$8,$D2,Sheet1!$B$2:$B$8,$A2,Sheet1!E$2:E$8,">0"),INDEX(Sheet1!E:E,AGGREGATE(15,6,ROW(Sheet1!E$2:E$8)/((Sheet1!$A$2:$A$8=$D2)*(Sheet1!$B$2:$B$8=$A2)),1)),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,441
Messages
5,624,796
Members
416,053
Latest member
bonalvero

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top