CODEWITHSUNDEEP
excelvbadatedatetime
сами J.D.
сами J.D.

Reputation: 513

Compare dates with specific datetime format (VBA)

EDIT: Based on answers I was able to get min/max date from a range:

Dim dt As Date
dt = WorksheetFunction.Min(Range("D2:D300"))

But it's not enough. How do I use this function with an array instead of a range?

Original post: I have the following columns:

enter image description here

The format is: DD/MM/YYYY HH:MM

I'm trying to get the soonest datetime from column one and the latest datetime from column two. In this case:

02/01/2017 6:07 (earlist datetime from the first column, 2nd of January)

02/02/2017 14:11 (latest datetime from the second column, 2nd of February)

I have a multidimensional array (myData) with the values from the cells and my functions are these ones:

Private Function GetLatestDateFromData() As String
  Dim latestDate As String
  Dim i As Long

  latestDate = myData(1, ColumnsIndex(3) - 1)

  For i = 1 To UBound(myData, 1) - 1
      If latestDate < myData(i, ColumnsIndex(3) - 1) Then
          latestDate = myData(i, ColumnsIndex(3) - 1)
      End If
  Next
  GetLatestDateFromData = latestDate
End Function

Private Function GetEarliestDateFromData() As String
  Dim earliestDate As String
  Dim i As Long

  earliestDate = myData(1, ColumnsIndex(2) - 1)

  For i = 1 To UBound(myData, 1) - 1
      If earliestDate > myData(i, ColumnsIndex(2) - 1) Then
          earliestDate = myData(i, ColumnsIndex(2) - 1)
      End If
  Next

  GetEarliestDateFromData = earliestDate
End Function

The problem is that my results are the following ones:

startingFrom = DateValue(GetEarliestDateFromData) 'returns 01/02/2017, 1st of February
untilDate = DateValue(GetLatestDateFromData) 'returns 01/06/2017, 1st of June

Seems I have a problem with the date formatting. Somehow, days and months are mixed. How do I fix it?

Thanks

EDIT: DateSerial (as suggested in a linked thread) does not apply here because I not only care about the date but the time as well. DateSerial only takes year-month-day as arguments.

Upvotes: 0

Views: 2131

Answers (1)

jSebesty&#233;n
jSebesty&#233;n

Reputation: 1806

To fix your dates use the format function, e.g.

date = Format(value, "MM\/DD\/YYYY")

More easily you could just compare the actual values (e.g. 02/01/2017 06:07 equals 42737,2548611111) which are independent of the displayed format.

Furthermore I'd suggest you use the WorksheetFunction.Max function which is the vba equvalent to excel Max-function, returning the greatest vaule in your range, something like:

date = WorksheetFunction.Max(your_used_range)

Upvotes: 1

Related Questions