Compare dates with specific datetime format (VBA)

Question

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:

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.

If those are really dates, then the format is completely irrelevant. Excel stores date/time as days and fractions of a day since 1 Jan 1900. =MIN(D:D) will return the earliest date/time in column D; =MAX(E:E) will return the latest date/time in column E — Ron Rosenfeld
– Ron Rosenfeld, Commented Jan 15, 2017 at 19:48
To use that function on an array, merely replace the range with a one-D array. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jan 15, 2017 at 20:47

jSebestyén · Accepted Answer · 2017-01-15 15:17:13Z

1

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)

answered Jan 15, 2017 at 15:17

jSebestyén

1,8362 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

сами J.D. Over a year ago

So, before doing any operation, I add the Format function. Will it understand automatically that the format is DD/MM and transform it to MM/DD...? How do I transform 02/01/2017 06:07 to 42737.25486... ? What function does this? That max function looks interesting. If you can answer these questions, I will understand it fully, thanks!

сами J.D. Over a year ago

Dim dt As Date dt = WorksheetFunction.Min(Range("D2:D300")), this works, but how do I do it with an array instead of using a Range?

Collectives™ on Stack Overflow

Compare dates with specific datetime format (VBA)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related