Next Date in Range (into next year)

[trdrc350]

I am using this formula to calculate the next date from my range of dates (dates are only m/d). How can i modify this so after the last day in the range it will go to next year?

MINIFS(J5:M5,J5:M5,">"&TODAY())

J5=1/8 K5-4/8 L5=7/8 M5=10/8

currently this is showing 1/0 as the date

 

[Joe4]

It is because none of the dates meet your criteria, all the dates are prior to today.

 

[trdrc350]

It is because none of the dates meet your criteria, all the dates are prior to today.

i would like the date of 1/8 to show. i guess the issue i'm having is getting it to roll over to next year

 

[Joe4]

i would like the date of 1/8 to show. i guess the issue i'm having is getting it to roll over to next year

The issue is that every valid date entry has a year portion, regardless of whether or not you show it your format (if you re-format that row to show years, you will see that).
If you enter a date entry without a year, Excel will automatically add the current year to it.

Of those 4 dates, are you just wanting it to return the NEXT of those four dates will occur, regardless of year?
Will the choices always be exactly these four dates, or could they be other dates too?
Perhaps you could supply a few more examples.

 

[trdrc350]

The issue is that every valid date entry has a year portion, regardless of whether or not you show it your format (if you re-format that row to show years, you will see that).
If you enter a date entry without a year, Excel will automatically add the current year to it.

Of those 4 dates, are you just wanting it to return the NEXT of those four dates will occur, regardless of year?
Will the choices always be exactly these four dates, or could they be other dates too?
Perhaps you could supply a few more examples.

these are the only choices. so i would like a formula to get the next date regardless of changing years. other examples i have are the same but different start dates in column J

 

[Joe4]

other examples i have are the same but different start dates in column J

Yes, please show me some of these other examples, so we can try to craft a solution that works for all cases, and not just this one case.

 

[trdrc350]

ok, i will show examples when i can get to my computer (where the file is)

 

[Joe4]

OK, the only good way I could think of doing this was creating my own User Defined Function in VBA.
The code for the function looks like this:

Function GetNextDate(rng As Range) As Date
'   Checks a the month and day of a range of dates and returns the one that appears next

    Dim cell As Range
    Dim m As Long
    Dim d As Long
    Dim y As Long
    Dim tdte As Date
    Dim dte As Date
    
'   Loop through all cells in range
    For Each cell In rng
'       Check to make sure date is a non-blank entry
        If cell.Value > 0 Then
'           Get month and day value of dates
            m = Month(cell)
            d = Day(cell)
'           Determine whether to use this year or next
            y = Year(Date)
            If (m < Month(Date)) Or ((m = Month(Date)) And (d <= Day(Date))) Then
'               Add one to year
                y = y + 1
            End If
'           Build new date
            tdte = DateSerial(y, m, d)
'           Save smallest calculated date
'           If initital value, save calculated value
            If dte = 0 Then
                dte = tdte
            Else
'               Check to see if new difference is less than saved one
                If (tdte - Date) < (dte - Date) Then
                    dte = tdte
                End If
            End If
        End If
    Next cell

'   Return value
    GetNextDate = dte
    
End Function

Then, once you have copy/pasted that in a new VBA module in your workbook, you can use it like any other Excel function.
So we would just enter the following formula in the cell where we wanted to return the value:

=GetNextDate(J5:M5)

Here are some examples of what it returns: