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]

format to your preferences
You can convert the new range to values and then delete the original data

Date and Time 2021.xlsm
	A	B	C	D	E	F	G
1	Date In and Out	ID	Name	Date	ID	Name	Start
2	13.01.2021 15:05	0000120066DF	Stefan Hellemans	13-Jan-21	0000120066DF	Stefan Hellemans	13-Jan-21 15:05
1c

Cell Formulas
Range	Formula
D2	D2	=--SUBSTITUTE(LEFT(A2,10),".","-")
E2:F2	E2	=B2
G2	G2	=D2+RIGHT(A2,5)

 

[ExcelMentee]

Thank you so much for giving the answer. But what about In and Out
how to fetch the Start and Stop.
Untitled Spreadsheet

Untitled Spreadsheet

	In and Out	ID	Name
	13.01.2021 15:05	0000120066DF	Stefan Hellemans
	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 14:59	0000120066DF	Stefan Hellemans
	11.01.2021 06:51	0000120066DF	Stefan Hellemans
	07.01.2021 09:33	0000120066DF	Stefan Hellemans
	06.01.2021 13:26	0000120066DF	Stefan Hellemans
Want this result from Above data
Data	ID	Name	Start	Stop
13-01-2021	0000120066DF	Stefan Hellemans	13.01.2021 06:56	13.01.2021 15:05
12-01-2021	0000120066DF	Stefan Hellemans	12.01.2021 07:01	12.01.2021 15:02
11-01-2021	0000120066DF	Stefan Hellemans	11.01.2021 06:51	11.01.2021 14:59
07-01-2021	0000120066DF	Stefan Hellemans	07.01.2021 09:33	If same date don’t found for stop it will be empty
06-01-2021	0000120066DF	Stefan Hellemans	06.01.2021 13:26

 

[ExcelMentee]

In = Start and Out = Stop

 

[Dave Patton]

Please clarify how you arrive at the start and end times
Adjust the formulas to show the information that you require.

Date and Time 2021.xlsm
	A	B	C	D	E	F
1	Date In and Out		ID	Name	Start	Stop
2	13.01.2021 15:05	13-Jan-21	0000120066DF	Stefan Hellemans	13-Jan-21 15:05
3	13.01.2021 11:56	13-Jan-21	0000149E0240	Lutgard Heremans	13-Jan-21 11:56
1c

Cell Formulas
Range	Formula
B2:B3	B2	=--SUBSTITUTE(LEFT(A2,10),".","-")
E2:E3	E2	=B2+RIGHT(A2,5)

 

[ExcelMentee]

The hours has been used as 1 to 24.
so the in time is will be early time and considered as Start.
Late time will be considered as Stop.

This is apply on same date.

 

[Dave Patton]

You can post an extract of your data with the forum's tool called XL2BB.

You can sort the data and then extract the data for start or end.

 

[Dave Patton]

Date and Time 2021.xlsm
	A	B	C	D	E	F
1	Date In and Out		ID	Name	Start	Stop
2	13.01.2021 15:05	13-Jan-21 15:05	0000120066DF	Stefan Hellemans	13-Jan-21 15:05	13-Jan-21 20:00
3	13.01.2021 20:00	13-Jan-21 20:00	0000120066DF	Stefan Hellemans	13-Jan-21 15:05	13-Jan-21 20:00
1c

Cell Formulas
Range	Formula
E2	E2	=MIN(B2:B3)
F2:F3	F2	=MAX(B2:B3)
E3	E3	=E2
B2:B3	B2	=--SUBSTITUTE(A2,".","-")

 

[sandy666]

with Power Query
(@post#3)

In and Out	ID	Name		Date	ID	Name	Start	Stop
13.01.2021 15:05	0000120066DF	Stefan Hellemans		13/01/2021	0000120066DF	Stefan Hellemans	13/01/2021 06:56	13/01/2021 15:05
13.01.2021 06:56	0000120066DF	Stefan Hellemans		12/01/2021	0000120066DF	Stefan Hellemans	12/01/2021 07:01	12/01/2021 15:02
12.01.2021 15:02	0000120066DF	Stefan Hellemans		11/01/2021	0000120066DF	Stefan Hellemans	11/01/2021 06:51	11/01/2021 14:59
12.01.2021 07:01	0000120066DF	Stefan Hellemans		07/01/2021	0000120066DF	Stefan Hellemans	07/01/2021 09:33
11.01.2021 14:59	0000120066DF	Stefan Hellemans		06/01/2021	0000120066DF	Stefan Hellemans	06/01/2021 13:26
11.01.2021 06:51	0000120066DF	Stefan Hellemans
07.01.2021 09:33	0000120066DF	Stefan Hellemans
06.01.2021 13:26	0000120066DF	Stefan Hellemans

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DT = Table.TransformColumnTypes(Source,{{"In and Out", type datetime}}),
    Date = Table.AddColumn(DT, "Date", each DateTime.Date([In and Out]), type date),
    Group = Table.Group(Date, {"ID", "Name", "Date"}, {{"Start", each List.Min([In and Out]), type datetime}, {"Max", each List.Max([In and Out]), type datetime}}),
    IF = Table.AddColumn(Group, "Stop", each if [Start] = [Max] then "" else [Max]),
    Type = Table.TransformColumnTypes(IF,{{"Stop", type datetime}}),
    TSC = Table.SelectColumns(Type,{"Date", "ID", "Name", "Start", "Stop"})
in
    TSC

 

[ExcelMentee]

Dave Patton​

I have just pasted the sample data this data comes in large size.
So filtration will take time.

here i have attached sheet please review

Google Sheets - create and edit spreadsheets online, for free.

Create a new spreadsheet and edit with others at the same time -- from your computer, phone or tablet. Get stuff done with or without an internet connection. Use Sheets to edit Excel files. Free from Google.

docs.google.com