Finding min and max from an array of dates

[RawlinsCross]

Not sure what I'm doing wrong here... I have a 1D array of dates and I want to use the Application.Worksheetfuntion.Max/Min to find the earliest and latest dates?

Thought this would be straight forward - any thoughts of what I'm doing wrong (I'm getting 12:00:00AM for both variables)? (see attached pic)

 

[Legacy 143009]

Dim vDateArray As Variant
vDateArray = Range("A1:A13").Value2

Try to cast Value2 property.

 

[Mario_R]

Hello.
How have you defined (Dim) the variable 'GetFirstLastDaiInArray'?...

 

[RawlinsCross]

Here's the function:

Public Function GetFirstLastDateInArray(vArray As Variant, sMode As String) As Date

Dim i As Long, j As Long
Dim vDateArray As Variant
Dim dDate As Date

ReDim vDateArray(1 To UBound(vArray, 1) - 1)

For i = LBound(vArray, 1) + 1 To UBound(vArray, 1)
    j = j + 1
    vDateArray(j) = CDate(vArray(i, 1))
Next i

If sMode = "First" Then
    dDate = Application.WorksheetFunction.Min(vDateArray)
ElseIf sMode = "Last" Then
    dDate = Application.WorksheetFunction.Max(vDateArray)
End If

GetFirstLastDateInArray = dDate

End Function

 

[kvsrinivasamurthy]

Try

Public Function GetFirstLastDateInArray(vArray As Range, sMode As String) As Date

If sMode = "First" Then
    GetFirstLastDateInArray = Application.WorksheetFunction.Min(vArray)
ElseIf sMode = "Last" Then
    GetFirstLastDateInArray = Application.WorksheetFunction.Max(vArray)
End If

End Function

 

[Legacy 143009]

What do you pass as vArray and how do you fill it?

 

[RawlinsCross]

vArray is a 2D array that I get from a csv file (wrote a function to do that). The dates are in the 3rd column.

If Not IsArrayAllocated(vDeliverables_) Then
        Application.ScreenUpdating = False
        vDeliverables_ = GetCSVInArray("DeliverablesDB.csv")
        Application.ScreenUpdating = True
End If

vArray = Application.Index(vDeliverables_, 0, 3)
mdStartDate = GetFirstLastDateInArray(vArray, "First")
mdEndDate = GetFirstLastDateInArray(vArray, "Last")

 

[Legacy 143009]

Oh you are using a csv file.. So the dates are not actual dates.. Hmm.. That's why you are casting to date. How about casting the date to a long and then convert it back?

Public Function GetFirstLastDateInArray(vArray As Variant, sMode As String) As Date

Dim i As Long, j As Long
Dim vDateArray As Variant
Dim dDate As Date

ReDim vDateArray(1 To UBound(vArray, 1) - 1)

For i = LBound(vArray, 1) + 1 To UBound(vArray, 1)
    j = j + 1
    vDateArray(j) = CLng(CDate(vArray(i, 1)))
Next i
With Application
If sMode = "First" Then
    dDate = CDate(.WorksheetFunction.Min(vDateArray))
ElseIf sMode = "Last" Then
    dDate = CDate(.WorksheetFunction.Max(vDateArray))
End If
End With
GetFirstLastDateInArray = dDate

End Function

 

[RawlinsCross]

Flashbond,

That did it! But I'm confused... the original function produced an array of dates (e.g. #2022-04-13#). Should not the min/max functions operate on these values?

 

[Legacy 143009]

Yeah, that's a strange behaviour. I have no idea why it is not working in vba. Although it's working fine with ranges. Maybe it's a problem with arrays. I don't know :/