Separate the In and Out Date from the Col"A"

[ExcelMentee]

when i download the data from software it comes in that shape.

Untitled Spreadsheet

Date In and Out	ID	Name
13.01.2021 15:05	0000120066DF	Stefan Hellemans
13.01.2021 11:56	0000149E0240	Lutgard Heremans
13.01.2021 08:20	0000149E0240	Lutgard Heremans
13.01.2021 06:56	0000120066DF	Stefan Hellemans
12.01.2021 15:02	0000120066DF	Stefan Hellemans
12.01.2021 07:01	0000120066DF	Stefan Hellemans
11.01.2021 15:34	0000149E0240	Lutgard Heremans
11.01.2021 14:59	0000120066DF	Stefan Hellemans
11.01.2021 08:24	0000149E0240	Lutgard Heremans
11.01.2021 06:51	000011FE671A	Stefan Hellemans
07.01.2021 09:33	000011FE671A	Stefan Hellemans
06.01.2021 13:26	000011FE671A	Stefan Hellemans

I want to convert this data into present able format that is

Data	ID	Name	Start	Stop
1/7/2021​	000011FE671A	Stefan Hellemans	07.01.2021 09:33
1/11/2021​	000011FE671A	Stefan Hellemans	11.01.2021 06:51	11.01.2021 14:59
1/11/2021​	0000149E0240	Lutgard Heremans	11.01.2021 08:24	11.01.2021 15:34
1/12/2021​	000011FE671A	Stefan Hellemans	12.01.2021 07:01	12.01.2021 15:02
1/13/2021​	000011FE671A	Stefan Hellemans	13.01.2021 06:56	13.01.2021 15:05
1/13/2021​	0000149E0240	Lutgard Heremans	13.01.2021 08:20	13.01.2021 11:56

First It will get the date in the format that i have use but i paste these dates manually because TEXT function is not working for this
Secondly Its name and ID's will paste accordingly.
Third one is the main formula or function that to separate the Start date and the Stop Date from the "Date In and Out" Column.

your help will be highly appreciated.

 

[Dave Patton]

Column A use find "." and replace "-" to make date
format the date and time to your preference
try the formulas N.B. You can copy the following example (see the icon below the F(x ) below) and paste into a clean sheet
Filter on Not Duplicate meaning 1

Date and Time 2021.xlsm
	A	B	C	D	E	F
1
2	Date In and Out	Name	Date	Start	Stop	Not Duplicate
3	6-Jan-21 13:26	Stefan Hellemans	6	6-Jan-21 13:26	6-Jan-21 13:26	1
4	7-Jan-21 09:33	Stefan Hellemans	7	7-Jan-21 09:33	7-Jan-21 09:33	1
5	11-Jan-21 06:51	Stefan Hellemans	11	11-Jan-21 06:51	11-Jan-21 14:59	1
6	11-Jan-21 08:24	Lutgard Heremans	11	11-Jan-21 08:24	11-Jan-21 15:34	1
9	12-Jan-21 07:01	Stefan Hellemans	12	12-Jan-21 07:01	12-Jan-21 15:02	1
11	13-Jan-21 06:56	Stefan Hellemans	13	13-Jan-21 06:56	13-Jan-21 15:05	1
12	13-Jan-21 08:20	Lutgard Heremans	13	13-Jan-21 08:20	13-Jan-21 11:56	1
15
1c (2)

Cell Formulas
Range	Formula
C11:C12,C9,C3:C6	C3	=DAY(A3)
D11:D12,D9,D3:D6	D3	=MIN(IF(($C$3:$C$14=C3)*($B$3:$B$14=B3),$A$3:$A$14))
E11:E12,E9,E3:E6	E3	=MAX(IF(($C$3:$C$14=C3)*($B$3:$B$14=B3),$A$3:$A$14))
F11:F12,F9,F3:F6	F3	=SUMPRODUCT(--($B$3:$B3=B3),--($C$3:$C3=C3))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dave Patton]

without Array Formulas

Date and Time 2021.xlsm
	A	B	C	D	E	F
16	Date In and Out	Name	Day	Start	End	Not Duplicate #1
17	6-Jan-21 13:26	Stefan Hellemans	6	6-Jan-21 13:26	6-Jan-21 13:26	1
18	7-Jan-21 09:33	Stefan Hellemans	7	7-Jan-21 09:33	7-Jan-21 09:33	1
19	11-Jan-21 06:51	Stefan Hellemans	11	11-Jan-21 06:51	11-Jan-21 14:59	1
20	11-Jan-21 08:24	Lutgard Heremans	11	11-Jan-21 08:24	11-Jan-21 15:34	1
21	11-Jan-21 14:59	Stefan Hellemans	11	11-Jan-21 06:51	11-Jan-21 14:59	2
22	11-Jan-21 15:34	Lutgard Heremans	11	11-Jan-21 08:24	11-Jan-21 15:34	2
23	12-Jan-21 07:01	Stefan Hellemans	12	12-Jan-21 07:01	12-Jan-21 15:02	1
24	12-Jan-21 15:02	Stefan Hellemans	12	12-Jan-21 07:01	12-Jan-21 15:02	2
25	13-Jan-21 06:56	Stefan Hellemans	13	13-Jan-21 06:56	13-Jan-21 15:05	1
26	13-Jan-21 08:20	Lutgard Heremans	13	13-Jan-21 08:20	13-Jan-21 11:56	1
27	13-Jan-21 11:56	Lutgard Heremans	13	13-Jan-21 08:20	13-Jan-21 11:56	2
28	13-Jan-21 15:05	Stefan Hellemans	13	13-Jan-21 06:56	13-Jan-21 15:05	2
1cc

Cell Formulas
Range	Formula
C17:C28	C17	=DAY(A17)
D17:D28	D17	=AGGREGATE(15,6,$A$17:$A$28/(($C$17:$C$28=C17)*($B$17:$B$28=B17)),1)
E17:E28	E17	=AGGREGATE(14,6,$A$17:$A$28/(($C$17:$C$28=C17)*($B$17:$B$28=B17)),1)
F17:F28	F17	=SUMPRODUCT(--($B$17:$B17=B17),--($C$17:$C17=C17))

filter by person and not showing extra data

Date and Time 2021.xlsm
	A	B	C	D	E	F
16	Date In and Out	Name	Day	Start	End	Not Duplicate #1
17	6-Jan-21 13:26	Stefan Hellemans	6	6-Jan-21 13:26	6-Jan-21 13:26	1
18	7-Jan-21 09:33	Stefan Hellemans	7	7-Jan-21 09:33	7-Jan-21 09:33	1
19	11-Jan-21 06:51	Stefan Hellemans	11	11-Jan-21 06:51	11-Jan-21 14:59	1
23	12-Jan-21 07:01	Stefan Hellemans	12	12-Jan-21 07:01	12-Jan-21 15:02	1
25	13-Jan-21 06:56	Stefan Hellemans	13	13-Jan-21 06:56	13-Jan-21 15:05	1
29
1cc

Cell Formulas
Range	Formula
C25,C23,C17:C19	C17	=DAY(A17)
D25,D23,D17:D19	D17	=AGGREGATE(15,6,$A$17:$A$28/(($C$17:$C$28=C17)*($B$17:$B$28=B17)),1)
E25,E23,E17:E19	E17	=AGGREGATE(14,6,$A$17:$A$28/(($C$17:$C$28=C17)*($B$17:$B$28=B17)),1)
F25,F23,F17:F19	F17	=SUMPRODUCT(--($B$17:$B17=B17),--($C$17:$C17=C17))

 

[ExcelMentee]

Dave Patton​

maabadi​

Thank you very very much for the help you guys provided is remarkable.

 

[maabadi]

You're Welcome & Thanks for Feedback.