How to convert, or parse, a string to work with dateserial VBA Excel

Question

I have a column of data which is composed of two elements (entered as a string). ``` 3/20/2016 6:30:55 PM 3/12/2016 8:15:45 PM 3/8/2016 1:25:18 AM ``` I want to set a variable to be equal to the DateSerial of the date portion of the string noted above. I do not want to pull the data apart and have it reside in separate columns. I wish to leave the formatting intact. I will be loading an array with all the data in the table (multiple columns) but I want the array to be indexed by date. I will run a quicksort on the array and then do some additional cross referencing with the sorted array. However, I cannot get to those stages, with the actual data set, as I'm stuck on the date issue. How can I index by date given the format of the source data? ``` Dim d as Date Dim arTemp Dim arTemp1 Dim WS As Worksheet Dim list As Object, list1 As Object Dim RowCount as Integer, y As Integer Set list = CreateObject("System.Collections.SortedList") Set list1 = CreateObject("System.Collections.SortedLis…

Accepted Answer

Have you tried using DateValue() instead of DateSerial()?

Using DateValue you can simply use the code

```
DateVariable = DateValue("10/30/2016 09:18 pm")
```

And it will resolve to

```
DateVariable = 10/30/2016
```

Should be a lot simpler than trying to use DateSerial

You can then add the following line

```
DateVariable = DateVariable - Day(DateVariable) + 1
```

To get to the first of the month

How to convert, or parse, a string to work with dateserial VBA Excel

Solution

Problem