How to change Autofill date entry year to 'next date in future', rather than 'current year', when entering 'day' and 'month' only?

[wiseone]

I enter a lot of dates in my spreadsheet, and to do so quickly, I enter the day, then month and hit enter. The resulting date is the date in the current calendar year. I prefer it to default to the upcoming date.

For example we are in 2023. If I enter 5-Jan, it will default to 5-Jan-2023, but I prefer it to default to 5-Jan-2024.

Possible?

 

[Joe4]

I would recommend doing it via VBA with a "Worksheet_Change" procedure, on a specified range of cells.

For example, if you wanted to apply this to column B, go to the sheet you want to apply it to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Make sure date entry in column B is for next year
    If Target.CountLarge = 1 And Target.Column = 2 And IsDate(Target) Then
'       If date entered is for current year, add one year to it
        If Year(Target) = Year(Date) Then
            Application.EnableEvents = False
            Target = DateSerial(Year(Date) + 1, Month(Target), Day(Target))
            Application.EnableEvents = True
        End If
    End If

End Sub

Now, whenever you enter a date with the current year in column B, it will automatically change it to next year.

 

[wiseone]

ok good solution I will consider, but is there also default option built into excel for this auto-populate?