Multiple conditions to return start/end time

[Peter Muller]

Good evening,

I have a table with information (Emp name, date, start and end times), and i require a formulae to search the emp name, date, start and end times.

Please help

Thank you

Personnel No.	First Name	Date	Time	Punch Status
000008633	Employee 1	2020-01-28	18:30:17	Check-Out
000008634	Employee 2	2020-01-28	18:24:47	Check-Out
000008635	Employee 3	2020-01-28	18:23:23	Check-Out
000008636	Employee 4	2020-01-28	18:20:06	Check-Out
000008633	Employee 1	2020-01-28	12:50:37	Check-In
000008635	Employee 3	2020-01-28	12:50:28	Check-In
000008634	Employee 2	2020-01-28	08:29:16	Check-In
000008636	Employee 4	2020-01-28	08:09:47	Check-In
000008634	Employee 2	2020-01-27	19:30:17	Check-Out
000008636	Employee 4	2020-01-27	19:20:06	Check-Out
000008633	Employee 1	2020-01-27	18:18:49	Check-Out
000008635	Employee 3	2020-01-27	17:51:01	Check-Out
000008635	Employee 3	2020-01-27	07:50:37	Check-In
000008634	Employee 2	2020-01-27	07:02:12	Check-In
000008636	Employee 4	2020-01-27	05:42:34	Check-In
000008633	Employee 1	2020-01-27	05:05:42	Check-In
Results Sheet
	2020-01-27	2020-01-27	2020-01-28	2020-01-28
Employee Name	Check-In	Check-Out	Check-In	Check-Out
Employee 1	Formulae?	Formulae?	Formulae?	Formulae?
Employee 2	07:02:12	19:30:17	08:29:16	18:24:47
Employee 3
Employee 4

 

[sandy666]

like this?

Min of Time	Date	Punch Status
	27/01/2020		28/01/2020
First Name	Check-In	Check-Out	Check-In	Check-Out
Employee 1	05:05:42	18:18:49	12:50:37	18:30:17
Employee 2	07:02:12	19:30:17	08:29:16	18:24:47
Employee 3	07:50:37	17:51:01	12:50:28	18:23:23
Employee 4	05:42:34	19:20:06	08:09:47	18:20:06

 

[jasonb75]

Try this

Book1
	A	B	C	D	E
1	Personnel No.	First Name	Date	Time	Punch Status
2	000008633	Employee 1	28/01/2020	18:30:17	Check-Out
3	000008634	Employee 2	28/01/2020	18:24:47	Check-Out
4	000008635	Employee 3	28/01/2020	18:23:23	Check-Out
5	000008636	Employee 4	28/01/2020	18:20:06	Check-Out
6	000008633	Employee 1	28/01/2020	12:50:37	Check-In
7	000008635	Employee 3	28/01/2020	12:50:28	Check-In
8	000008634	Employee 2	28/01/2020	08:29:16	Check-In
9	000008636	Employee 4	28/01/2020	08:09:47	Check-In
10	000008634	Employee 2	27/01/2020	19:30:17	Check-Out
11	000008636	Employee 4	27/01/2020	19:20:06	Check-Out
12	000008633	Employee 1	27/01/2020	18:18:49	Check-Out
13	000008635	Employee 3	27/01/2020	17:51:01	Check-Out
14	000008635	Employee 3	27/01/2020	07:50:37	Check-In
15	000008634	Employee 2	27/01/2020	07:02:12	Check-In
16	000008636	Employee 4	27/01/2020	05:42:34	Check-In
17	000008633	Employee 1	27/01/2020	05:05:42	Check-In
18
19	Results Sheet
20		27/01/2020	27/01/2020	28/01/2020	28/01/2020
21	Employee Name	Check-In	Check-Out	Check-In	Check-Out
22	Employee 1	05:05:42	18:18:49	12:50:37	18:30:17
23	Employee 2	07:02:12	19:30:17	08:29:16	18:24:47
24	Employee 3	07:50:37	17:51:01	12:50:28	18:23:23
25	Employee 4	05:42:34	19:20:06	08:09:47	18:20:06
Sheet1

Cell Formulas
Range	Formula
B22:E25	B22	=SUMIFS($D$2:$D$17,$B$2:$B$17,$A22,$C$2:$C$17,B$20,$E$2:$E$17,B$21)

Note that if an employee punches in twice, or out twice in the same day then the results will be incorrect. You could avoid this by using MINIFS or MAXIFS instead of SUMIFS as long as you are using excel 2019 or office 365. Older versions do not support these functions.

 

[Peter Muller]

Try this

Book1
	A	B	C	D	E
1	Personnel No.	First Name	Date	Time	Punch Status
2	000008633	Employee 1	28/01/2020	18:30:17	Check-Out
3	000008634	Employee 2	28/01/2020	18:24:47	Check-Out
4	000008635	Employee 3	28/01/2020	18:23:23	Check-Out
5	000008636	Employee 4	28/01/2020	18:20:06	Check-Out
6	000008633	Employee 1	28/01/2020	12:50:37	Check-In
7	000008635	Employee 3	28/01/2020	12:50:28	Check-In
8	000008634	Employee 2	28/01/2020	08:29:16	Check-In
9	000008636	Employee 4	28/01/2020	08:09:47	Check-In
10	000008634	Employee 2	27/01/2020	19:30:17	Check-Out
11	000008636	Employee 4	27/01/2020	19:20:06	Check-Out
12	000008633	Employee 1	27/01/2020	18:18:49	Check-Out
13	000008635	Employee 3	27/01/2020	17:51:01	Check-Out
14	000008635	Employee 3	27/01/2020	07:50:37	Check-In
15	000008634	Employee 2	27/01/2020	07:02:12	Check-In
16	000008636	Employee 4	27/01/2020	05:42:34	Check-In
17	000008633	Employee 1	27/01/2020	05:05:42	Check-In
18
19	Results Sheet
20		27/01/2020	27/01/2020	28/01/2020	28/01/2020
21	Employee Name	Check-In	Check-Out	Check-In	Check-Out
22	Employee 1	05:05:42	18:18:49	12:50:37	18:30:17
23	Employee 2	07:02:12	19:30:17	08:29:16	18:24:47
24	Employee 3	07:50:37	17:51:01	12:50:28	18:23:23
25	Employee 4	05:42:34	19:20:06	08:09:47	18:20:06
Sheet1

Cell Formulas
Range	Formula
B22:E25	B22	=SUMIFS($D$2:$D$17,$B$2:$B$17,$A22,$C$2:$C$17,B$20,$E$2:$E$17,B$21)

Note that if an employee punches in twice, or out twice in the same day then the results will be incorrect. You could avoid this by using MINIFS or MAXIFS instead of SUMIFS as long as you are using excel 2019 or office 365. Older versions do not support these functions.

Thank you so much,

It works and i am most thankful indeed.