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

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
145
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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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:
VBA Code:
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.
 
Upvote 0
ok good solution I will consider, but is there also default option built into excel for this auto-populate?
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,215
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top