CODEWITHSUNDEEP
vbaexcelbasic
S. Deans
S. Deans

Reputation: 43

Trying to make a Collection of Dates from concatenated Strings

I am making a macro where I search through a spreadsheet of dates where each component of a date is already broken into separate components ie, year, month, day, hour, minute, second.

I want to compare these dates and find the earliest and latest date.

I was trying to concatenate the date components from strings into a Date variable, store the Dates in a Collection, and then search the Collection for the earliest and latest dates.

Dim dt As Date
Dim dtstring As String

Dim dates As New Collection

... [code where I just set unrelated variable values from cells and started a loop]

dtstring = "#" & day & "/" & month & "/" & year & " " & hour & ":" & minute & ":" & second & "#"
dt = dtstring
dates.Add dt 'add a Date into the dates collection

It doesn't like setting my Date variable value, dt, from a string but every example I find of this hard codes the Date variable with something similar to

Dim dteAppointment As Date = #8/2/2007 14:02:00#

whereas I need it set by the six date components.

Any help on this is much appreciated.

Upvotes: 1

Views: 236

Answers (1)

Samuel Hulla
Samuel Hulla

Reputation: 7089

Under presumption you still have the original dates available, simply use:

Application.WorksheetFunction.Max(<Range>)

and

Application.WorksheetFunction.Min(<Range>)

respectively.

This will already return the result for you, without needing to do any crazy string formatting and what not.

EDIT: Probably worth noting, the returned value is Double type. It's not that big of a deal, given the function automatically serializes the input dates, but if you're returning the result to a variable, make sure to return it to a Dim resAs Double and not As Date

Afterwards, if you wish to return it back to a specific cell or somewhere as a Date, simply use the inbuilt DateSerial() in-built method! ;)

Upvotes: 1

Related Questions