CODEWITHSUNDEEP
excelvbamaxmaxdate
Reyk
Reyk

Reputation: 13

VBA get max date from the format YYYY-DD-MM (like 2023-10-02)

In VBA I want to extract the max date (or max value) from a range, but I don't want to change the format of the range. Now it looks like below:

Date:
2023-10-02
2023-10-03
2023-10-04
2023-10-05
2023-10-06
2023-10-07

But the normal max function is not working in VBA...

There are also empty cells in this column, so it would be good to not consider these ones.

Thank you in advance and best regards!

Upvotes: 1

Views: 242

Answers (2)

Алексей Р
Алексей Р

Reputation: 7627

This function can be used both in a worksheet and in VBA

Option Explicit

Function GetMaxDate(rng As Range) As Variant
    Dim cur_date As Date, arr As Variant, d As Variant
    arr = rng ' get all the data into an array to improve performance (one operation of reading data from the sheet)
    For Each d In arr
        If IsDate(d) Then ' we check whether the next value can be a date
            cur_date = CDate(d) '
            If GetMaxDate < cur_date Then GetMaxDate = cur_date ' select max date
        End If
    Next
    If Not IsEmpty(GetMaxDate) Then
        GetMaxDate = Format(GetMaxDate, "yyyy-mm-dd")
    Else
        GetMaxDate = "#NODATE"
    End If
End Function

enter image description here

Upvotes: 1

taller
taller

Reputation: 18763

You can utilize the worksheet function Max to obtain the maximum date, and the return value will be in the form of a double.

In cases where A1 might be empty, using Range("A1").End(xlDown) will locate the next non-blank cell. If column A follows the same format throughout, then Range("A1").NumberFormatLocal should suffice.

Debug.Print Format(Application.WorksheetFunction.Max(Range("A:A")), Range("A1").End(xlDown).NumberFormatLocal)

If you have a clear understanding of the expected output format, it can lead to simpler code.

Debug.Print Format(Application.WorksheetFunction.Max(Range("A:A")), "yyyy-mm-dd")

Upvotes: 1

Related Questions