Reputation: 547

VBA: Converting String to DateTime for entire column

I am importing a load of time-stamped data into a spreadsheet using VBA.

I am using the following code to import it:

Private Sub SelectFiles()
    '
    ' Part of a form for importing the date
    '
    
    Dim iFileSelect As FileDialog, sFile
    Set iFileSelect = Application.FileDialog(msoFileDialogOpen)
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' Run file dialoug
    With iFileSelect
        .AllowMultiSelect = False
        .Title = "Select NPD File"
        .Filters.Clear
        .Filters.Add "NPD Files", "*.npd, *.NPD"
        .InitialView = msoFileDialogViewDetails
        If .Show = -1 Then
            sFile = .SelectedItems(1)
            Workbooks.Open Filename:=sFile, _
                Delimiter:=","
        End If
    End With
    
    ' Run deliminations (the Dimiliter in the import didn't seem to work
    Columns("A:A").TextToColumns _
        Destination:=Range("A1"), _
        DataType:=xlDelimited, _
            Space:=False, _
            Comma:=True
    
    ' Update a text box in the form
    tb_ImportData.Value = sFile
    
    Set iFileSelect = Nothing
    
    Application.ScreenUpdating = True
    
End Sub

An example of the Raw data file is:

Time,Position: Vessel Ref (Priority 1): East,North,Lat,Long,Height,Position: Vessel Ref (Priority 2): East,North,Lat,Long,Height,Position: Vessel Ref (Priority 3): East,North,Lat,Long,Height,Position: Veripos 1 Port: East,North,Lat,Long,Height,Position: Veripos 2 Stbd: East,North,Lat,Long,Height,Position: Veripos 3 Survey: East,North,Lat,Long,Height,Gyro: Seapath 1,Gyro: Seapath 2,Gyro: Octans - Port,Gyro: Octans - Stbd,Gyro: Anschutz 1,Gyro: Anschutz 2,Motion: Seapath RPH1 R, P, H,Motion: Seapath RPH2 R, P, H,Motion: Vsl Oct MRU - Port R, P, H,Motion: Vsl Oct MRU - Stbd R, P, H,Motion: MRU1 R, P, H,Motion: MRU2 R, P, H,
14/01/2021 15:38:55,  340828.7321,5482873.9254,  049°28'39.44929",  000°48'9.54126", 49.5634,  340828.7697,5482873.8963,  049°28'39.44838",  000°48'9.54317", 49.5200,  340828.7905,5482873.8792,  049°28'39.44785",  000°48'9.54423", 49.5479,  340799.5217,5482888.9646,  049°28'39.90835",  000°48'8.06891", 87.3525,  340800.1545,5482890.0231,  049°28'39.94320",  000°48'8.09880", 87.3100,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0900,  296.3800,  294.5400,  295.6100,  296.9500,  296.2000,  296.7000,    0.2000,   0.1800,   0.0000,    0.1700,   0.1500,   0.0100,   -2.0900,   0.3300,   0.0000,    2.6000,  -0.1800,   0.0000,    0.1894,   0.1816,   3.2660,    0.1901,   0.1553,   2.5260
14/01/2021 15:38:56,  340828.7283,5482873.9168,  049°28'39.44901",  000°48'9.54108", 49.5664,  340828.7697,5482873.8963,  049°28'39.44838",  000°48'9.54317", 49.5300,  340828.7843,5482873.8687,  049°28'39.44750",  000°48'9.54393", 49.5485,  340799.5236,5482888.9670,  049°28'39.90843",  000°48'8.06900", 87.3556,  340800.1545,5482890.0231,  049°28'39.94320",  000°48'8.09880", 87.3200,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0900,  296.3800,  294.5400,  295.6100,  296.9600,  296.2000,  296.7000,    0.2000,   0.1800,   0.0000,    0.1900,   0.1600,   0.0100,   -2.0900,   0.3300,   0.0000,    2.6000,  -0.1800,   0.0000,    0.1902,   0.1824,   2.8160,    0.1917,   0.1567,   2.2100
14/01/2021 15:38:57,  340828.7320,5482873.9302,  049°28'39.44944",  000°48'9.54125", 49.5632,  340828.7545,5482873.8908,  049°28'39.44819",  000°48'9.54242", 49.5302,  340828.7843,5482873.8687,  049°28'39.44750",  000°48'9.54393", 49.5385,  340799.5221,5482888.9702,  049°28'39.90853",  000°48'8.06892", 87.3524,  340800.1425,5482890.0235,  049°28'39.94320",  000°48'8.09820", 87.3200,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0800,  296.3600,  294.5300,  295.6200,  296.9600,  296.2000,  296.7000,    0.2000,   0.1800,   0.0000,    0.1900,   0.1600,   0.0100,   -2.0900,   0.3300,   0.0000,    2.6100,  -0.1800,   0.0000,    0.1910,   0.1827,   2.2890,    0.1924,   0.1573,   1.8520
14/01/2021 15:38:58,  340828.7220,5482873.9107,  049°28'39.44880",  000°48'9.54078", 49.5605,  340828.7584,5482873.8976,  049°28'39.44841",  000°48'9.54260", 49.5302,  340828.7905,5482873.8780,  049°28'39.44781",  000°48'9.54423", 49.5385,  340799.5235,5482888.9725,  049°28'39.90861",  000°48'8.06898", 87.3499,  340800.1425,5482890.0235,  049°28'39.94320",  000°48'8.09820", 87.3200,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0800,  296.3800,  294.5300,  295.6200,  296.9600,  296.2000,  296.7000,    0.2200,   0.1800,   0.0000,    0.2000,   0.1600,   0.0000,   -2.0800,   0.3300,   0.0000,    2.6100,  -0.1700,   0.0000,    0.1922,   0.1832,   1.5800,    0.1942,   0.1577,   1.1350
14/01/2021 15:38:59,  340828.7370,5482873.9382,  049°28'39.44971",  000°48'9.54148", 49.5554,  340828.7662,5482873.8896,  049°28'39.44816",  000°48'9.54300", 49.5202,  340828.7781,5482873.8571,  049°28'39.44712",  000°48'9.54364", 49.5396,  340799.5266,5482888.9775,  049°28'39.90877",  000°48'8.06913", 87.3445,  340800.1545,5482890.0231,  049°28'39.94320",  000°48'8.09880", 87.3100,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0800,  296.3700,  294.5400,  295.6200,  296.9600,  296.2000,  296.7000,    0.1900,   0.1800,   0.0000,    0.2000,   0.1600,   0.0000,   -2.0800,   0.3400,   0.0000,    2.6100,  -0.1700,   0.0000,    0.1944,   0.1840,   7.3700,    0.1953,   0.1574,   1.4510
14/01/2021 15:39:00,  340828.7400,5482873.9400,  049°28'39.44977",  000°48'9.54163", 49.5581,  340828.7673,5482873.9157,  049°28'39.44901",  000°48'9.54302", 49.5174,  340828.7886,5482873.8778,  049°28'39.44780",  000°48'9.54413", 49.5372,  340799.5296,5482888.9792,  049°28'39.90883",  000°48'8.06928", 87.3472,  340800.1551,5482890.0416,  049°28'39.94380",  000°48'8.09880", 87.3100,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0800,  296.3700,  294.5400,  295.6300,  296.9700,  296.2000,  296.7000,    0.2200,   0.1900,   0.0000,    0.1900,   0.1600,   0.0000,   -2.0800,   0.3400,   0.0000,    2.6100,  -0.1700,   0.0000,    0.1970,   0.1838,  -4.0400,    0.1968,   0.1564,  -9.3500
14/01/2021 15:39:01,  340828.7278,5482873.9244,  049°28'39.44925",  000°48'9.54105", 49.5462,  340828.7676,5482873.9143,  049°28'39.44896",  000°48'9.54304", 49.5183,  340828.7799,5482873.8688,  049°28'39.44750",  000°48'9.54372", 49.5354,  340799.5325,5482888.9780,  049°28'39.90880",  000°48'8.06942", 87.3413,  340800.1551,5482890.0416,  049°28'39.94380",  000°48'8.09880", 87.3100,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0800,  296.3600,  294.5400,  295.6300,  296.9700,  296.2000,  296.7000,    0.2000,   0.1800,   0.0000,    0.2000,   0.1600,   0.0000,   -2.0800,   0.3400,   0.0000,    2.6100,  -0.1700,   0.0000,    0.1981,   0.1823,  -1.4600,    0.1982,   0.1555,  -1.9200
14/01/2021 15:39:02,  340828.7411,5482873.9428,  049°28'39.44986",  000°48'9.54168", 49.5509,  340828.7676,5482873.9209,  049°28'39.44918",  000°48'9.54303", 49.5154,  340828.7873,5482873.8791,  049°28'39.44784",  000°48'9.54407", 49.5257,  340799.5319,5482888.9825,  049°28'39.90894",  000°48'8.06939", 87.3407,  340800.1551,5482890.0416,  049°28'39.94380",  000°48'8.09880", 87.3100,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0700,  296.3500,  294.5400,  295.6300,  296.9800,  296.2000,  296.7000,    0.2100,   0.1800,  -0.0100,    0.2000,   0.1600,   0.0000,   -2.0700,   0.3300,   0.0000,    2.6100,  -0.1700,   0.0000,    0.1993,   0.1809,  -2.3800,    0.1999,   0.1531,  -2.7500

The date/time string is coming in the correct format (e.g. 14/01/2021 16:08:57). This is an example of the data:

Here, the date-time is in a string format. However, if you select a cell and press F2, it will convert it into a DateTime format.

I am able to use VBA to convert each cell individually into DateTime format as well. However, I have over 10,000 rows in this dataset and thus is loop is taking too long.

Is it possible to convert the entire column without having to loop through the individual cells?

Upvotes: 0

Answers (3)

norie

Reputation: 9857

When using TextToColumns you can specify the data format for the columns via the FieldInfo argument.

To specify that the column is a date with the format YMD you would use xlYMDFormat.

Columns("A:A").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, _
        Space:=False, _
        Comma:=True, FieldInfo:=Array(Array(1, xlYMDFormat))

Upvotes: 0

Storax

Reputation: 12167

Try this procedure to convert the text into date. As I copy the data into memory this is a lot faster than doing it in the worksheet

Sub convDate()

    Dim vDat As Variant
    vDat = Range("A2:A1000")
    
    
    Dim i As Long
    For i = LBound(vDat) To UBound(vDat)
        vDat(i, 1) = CDate(vDat(i, 1))
    Next
    Range("A2:A10000") = vDat
    
End Sub

Upvotes: 1

zedfoxus

Reputation: 37029

Assuming your date is in B2, in C2 type this:

=Date(RIGHT(LEFT(B2, 10), 4), RIGHT(LEFT(B2, 5), 2), LEFT(B2, 2))
+
Time(RIGHT(LEFT(B2, 13), 2), RIGHT(LEFT(B2, 16), 2), RIGHT(B2, 2))

Copy that down the column C.

Explanation

We are using the =Date(year, month, day) and =Time(hour, minutes, seconds) functions in Excel.

If time is 14/01/2021 16:08:57, LEFT(B2, 10) will give you 14/01/2021. RIGHT(LEFT(B2, 10), 4) will give you 2021. Using that method, we pluck year, month and date and pass it to Date function.

We repeat that with Time function and add them up.

Upvotes: 1

VBA: Converting String to DateTime for entire column

Answers (3)

Related Questions