Excel - Find the last day of a month in a range given a specific year and month value

Question

I am wondering what Excel/VBA functions I can use to find the last day of a month in a range with the specific year and month input values. For example, with '1995' and '3', it should return '3/31/1995'. With '1995' and '4', it should return '4/28/1995'.

Note that the actual last day of '04/1995' was '4/30/1995'. I am looking for the last day in the range, '4/28/1995', so I can't just blindly use the EOMONTH function.

Answer 1

Below is a VBA solution that should work and be relatively fast.

I'm adding all items in the range that match the year and month to an ArrayList. Then, I'm sorting that list in ascending order and picking the last item in the list (this item should possess the largest value in the set).

This is running in less than a second going through a list of about 800 items.

Function:

Option Explicit

Public Function MaxDateInRange(SearchRange As Range, _
                               YearNumber As Long, _
                               MonthNumber As Long) As String
    Dim cell        As Range
    Dim ListIndex   As Long
    Dim List        As Object: Set List = CreateObject("System.Collections.ArrayList")
    
    'Go through all cells, and all items that match the month and year to a list
    For Each cell In SearchRange
        If IsDate(cell) Then
            If Month(cell) = MonthNumber And Year(cell) = YearNumber Then List.Add (cell)
        End If
    Next
    
    'Sort the list ascending, then select the last item in that list
    List.Sort
    ListIndex = List.Count - 1
    
    'Bounds check, to see if anything was found, otherwise return ""
    If ListIndex >= 0 Then
        MaxDateInRange = List(ListIndex)
    Else
        MaxDateInRange = vbNullString
    End If
    
End Function

Usage:

Public Sub Example()
    Dim rng As Range: Set rng = Sheets(2).Range("D1:D795")
    Dim t   As Double
    t = Timer
    
    Debug.Print MaxDateInRange(rng, 2019, 3)
    Debug.Print MaxDateInRange(rng, 2019, 4)

    Debug.Print "Process took " & Timer - t
End Sub

Debug Output based on sample data:

2019-03-28
2019-04-25
Process took 0.04296875

Answer 2

Another method could be as follows, I've not fully tested, but could be food for thought.

Function get_latest_date(rngInput As Excel.Range, intMonth As Integer, lngYear As Long) As Date

get_latest_date = 0

On Error Resume Next

get_latest_date = Application.Evaluate( _
                "=MAX(IF((YEAR(" & _
                rngInput.Address & _
                ")=" & lngYear & _
                ")*(MONTH(" & _
                rngInput.Address & _
                ")=" & intMonth & ")," & rngInput.Address & "))")

End Function

This uses the evaluation of an array formula built from arguments passed in.

I have dummy dates, 10,000 in total, from 2015 to over 2030. I ran a quick test using the below

Function test_get_last_date()

Dim r As Excel.Range
Dim lYear As Long
Dim iMonth As Integer
Dim dTimer As Double

Set r = Range("a1:a10000")

dTimer = Timer

For lYear = 2015 To 2030

    For iMonth = 1 To 12

        Debug.Print get_latest_date(r, iMonth, lYear), "Took : "; Timer - dTimer
        dTimer = Timer

    Next iMonth

Next lYear


End Function

This gave these results

31/05/2017    Took :  0.02734375 
30/06/2017    Took :  0.015625 
31/07/2017    Took :  0.015625 
31/08/2017    Took :  0.015625 
30/09/2017    Took :  0.01953125

Answer 3

You have 2 options :

1. Your data are sorted and you can use match with 1 or -1 third option. As comment from Darren Bartrup-Cook says

2. Else you have to add 2 columns of formula to sort your solution:

Column B, formula =year(A:A)&MONTH(A:A) ; concatenate your criteria

Column C, formula from cell C2 =IFERROR(MAX((B$1:B1=B2)*(C$1:C1)),A2) ; then expand formula down

The last value in column C for each unique month in column B will be your answers. You can extract results in Column D with formula from cell D2 =MAX(IF(B:B=B2,C2)) ; then expand formula down