I have the data in excel like this, one column:
10/15/2021 7:59:42 AM
10/15/2021 7:59:44 AM
10/15/2021 7:59:46 AM
.
.
.
10/16/2021 7:59:42 AM
10/16/2021 7:59:48 AM
10/16/2021 7:59:49 AM
I used this code in VBA to create two columns, one with date and one with time:
Sub CommandButton1_Click()
Dim rng As Range
Set rng = [A1]
Set rng = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))
rng.Texttocolumns Destination:=[B1], DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, xlMDYFormat), Array(2, xlMDYFormat), Array(3, xlGeneralFormat)), _
TrailingMinusNumbers:=True
Columns("C").Delete
Columns("E").Delete
Columns("D").Delete
End Sub
After that, I have two columns. My goal is to have one column with date and time, but to have date only for the first time when it appears. I have done this with excel using IF and later CONCAT. I copied first row and for the second I used:
IF(B3=B2,"",B3)
The result is empty cell, I just then use CONCAT with row which contains time. I would love to do this in VBA, but I am not sure how.
Aucun commentaire:
Enregistrer un commentaire