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.

 

[ExcelMentee]

sandy666​

Power Query is different thing.
I did not used it before so it will take time to understand power Query

 

[sandy666]

​

Power Query is different thing.
I did not used it before so it will take time to understand power Query

sure, no problem
here is almost everything about Power Query

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

btw. your file on GoogleDrive isn't shared

 

[ExcelMentee]

I am reviewing you last answer and file is shared now.

 

[sandy666]

I am reviewing you last answer and file is shared now.

Ok, but this is the same as in post#3 so nothing new

 

[ExcelMentee]

sandy666 it will be huge help if you could create it through Formula or VBA​

 

[sandy666]

sandy666 it will be huge help if you could create it through Formula or VBA​

sorry mate but I prefer Power Query
maybe someone else will do that for you

 

[ExcelMentee]

Thank you for the assistance you have provided

sandy666.​

 

[sandy666]

Thank you for the assistance you have provided

You are welcome and good luck with another type of solutions

 

[maabadi]

Try this:
I used column D to K, if they aren't empty Change Columns.

Sub SumifsMultyWS()
Dim Ws As Worksheet, S As Double, Lr As Long, Ws1 As Worksheet, i As Long, T As Long
Dim j As Long, K As Double
Lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:C" & Lr).Sort key1:=Columns("B"), Order1:=xlAscending, Key2:=Columns("A"), Order2:=xlAscending, Header:=xlYes
Range("D1").Value = "MainDate"
Range("G1").Value = "ID"
Range("H1").Value = "Name"
Range("I1").Value = "Date"
Range("J1").Value = "Start Time"
Range("K1").Value = "End Time"
For i = 2 To Lr
Range("D" & i).Value = Int(Range("A" & i).Value)
Range("E" & i).Value = Range("A" & i).Value - Range("D" & i).Value
Next i

Range("B2:D" & Lr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G2:I2"), Unique:=True

For i = 2 To Lr
For j = 2 To Lr
If Range("I" & i).Value = Range("D" & j).Value And Range("G" & i).Value = Range("B" & j).Value Then
T = j - 1
If Range("J" & i).Value = "" Then
S = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Index(Range("A2:A" & Lr), T), "")
Range("J" & i).Value = S - Range("I" & i).Value
End If
If Range("E" & j).Value > Range("J" & i).Value Then
K = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Index(Range("A2:A" & Lr), T), "")
Range("K" & i).Value = K - Range("I" & i).Value
End If
End If
Next j
Next i

End Sub

 

[Dave Patton]

Column A I used find and replace to make date
I sorted by name & Date

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

Cell Formulas
Range	Formula
C6:C17	C6	=DAY(A6)
D6	D6	=A6
E6:E17	E6	=IF(C6=C7,MAX(A6:A7),"")
F6:F17	F6	=AGGREGATE(14,6,A$6:A$17/($B$6:$B$17=$B6)*($C$6:$C$17=C6),1)
D7:D17	D7	=IF(AND(C7=C6,B7=B6),"",A7)