1

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.

S. Deans
  • 43
  • 4
  • https://stackoverflow.com/questions/2636157/excel-date-to-string-conversion and https://stackoverflow.com/a/36605896/2727437 – Marcucciboy2 Aug 20 '18 at 16:58

1 Answers1

1

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! ;)

Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70
  • Thanks for your help! My original dates are in four cells per row. There is one for the date, hours, minutes and seconds. (I made the macro break the date cell into year, month and day components earlier) Do you think I could feed that into your method by arranging it somehow first? I need to be able to compare the date down to the second – S. Deans Aug 20 '18 at 17:24
  • Yes, look at the link of the [`DateSerial()`](https://www.techonthenet.com/excel/formulas/dateserial.php) method closely in my original answer. Or to be even more precise, the arguments that you 'feed' it. ;) *\*hint hint\** – Samuel Hulla Aug 20 '18 at 17:27
  • DateSerial is only taking in year, month, and day. I need it to take the time of day too, ie hours minutes and seconds. I see there is a TimeSerial function which takes hours, minutes, and seconds. A combination of these two functions might be the way – S. Deans Aug 20 '18 at 17:32
  • In that case, let me introduce you to `DateSerial()`'s cousin, [`TimeSerial()`](https://www.techonthenet.com/excel/formulas/timeserial.php) and you then combine the two together as linked in the comments (usually done via `TEXT()`) – Samuel Hulla Aug 20 '18 at 17:34
  • 1
    @QHarr Only for those who dare to use it! *(obviously a typo, hehe)* – Samuel Hulla Aug 20 '18 at 17:38
  • The text() function returns a string value. I need to be able to evaluate these dates and times and find the earliest and latest. I'm not following how you imagine doing that with a returned string – S. Deans Aug 20 '18 at 17:41
  • I don't have access to Excel atm, so I can't give you too indepth code without it risking being incorrect, but basically, here's how I would go about things if i were to implement an algorithm. 1. Create an extra column/array where I'll `DateSerial()` individual dates. 2. Create a extra column/array where I'll `TimeSerial()` individual times. 3. Use `Min/Max` on Dates. If there is only one, return it and the time next to it, after it's concatenated and fed into `TEXT()` function. If there are multiple mins/maxs on same date use `Min/max` on Times as well for the same dates and voila! – Samuel Hulla Aug 20 '18 at 17:46