VBA & Macro Problems

How to Enter a Date Without Typing the Year in Excel Using VBA

Kushani Nimanthika Nov 13, 2026 868 views

Question details

The user needs a way to type a short numeric date, such as 0312, in Excel and have it consistently converted into a real date with the current or intended year without manually typing the year.

Enter a Date Without Typing the Year in Excel Using VBA

Product: Excel
Device & OS: not provided
Scenario: The user enters three- or four-digit values representing day and month and wants Excel to recognize the intended date automatically.
Observed behavior: Excel interprets short entries according to regional date settings and may assign the current year automatically, but it does not consistently preserve the intended day-month order.

Before you start

Open the workbook where you want to use the short date entry. Confirm whether your system uses day-month or month-day date order, because the VBA assignments must match your regional format.

Solution 1Recommended

Use a Worksheet_Change VBA macro to convert short entries into dates

Use this solution when you want Excel to automatically turn typed values like 0312 or 312 into a real date without editing each cell manually.

This macro runs whenever a cell in the monitored range changes. It reads the entry as text before Excel's regional date interpretation can create an unexpected date, then uses DateSerial to build the date from the day and month digits and the current year.

Open the worksheet module

In your Excel workbook, right-click the sheet tab where you enter dates and select View Code. This opens the Visual Basic Editor with the worksheet module active.

Paste the Worksheet_Change macro

Copy the following macro into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cel As Range Dim d As Long Dim m As Long Dim y As Long Set rng = Range("A2:A100") If Intersect(rng, Target) Is Nothing Then Exit Sub y = Year(Date) On Error GoTo ExitHere Application.ScreenUpdating = False Application.EnableEvents = False For Each cel In Intersect(rng, Target) If Len(cel.Formula) > 0 Then Select Case Len(cel.Formula) Case 3 d = Left(cel.Formula, 1) m = Right(cel.Formula, 2) cel.Value = DateSerial(y, m, d) Case 4 d = Left(cel.Formula, 2) m = Right(cel.Formula, 2) cel.Value = DateSerial(y, m, d) Case Else Application.Undo End Select End If Next cel ExitHere: Application.EnableEvents = True Application.ScreenUpdating = True End Sub

Adjust the target range and day-month order if needed

Change Range("A2:A100") to the range where you enter dates. If you use month-day order, swap the month and day assignments in the code so 0312 becomes March 12 instead of December 3. If you need a fixed year instead of the current year, replace y = Year(Date) with y = 2023 or your chosen year.

Save as a macro-enabled workbook and enable macros

Select File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and save the file. When you reopen it, click Enable Content or Enable Macros so the Worksheet_Change event can run.

Test the date entry

Type 0312 or 312 into one of the monitored cells and press Enter. The cell should change to the corresponding date in the current year according to the day-month order configured in the code.

Macro saved correctly: This macro only runs for the sheet module where it is installed. Save the workbook as .xlsm to keep the code with the file.

WPS Spreadsheets VBA date entry

Use WPS Spreadsheets to Enter Dates Without Typing the Year

WPS Spreadsheets supports the same Worksheet_Change VBA event, so you can add the date-entry macro and automatically convert short numeric dates without manually typing the year.

1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook where you want to enter dates without typing the year.
2. Open the VBA editor from the sheet tab: Right-click the sheet tab and choose View Code to open the Visual Basic Editor for the worksheet module.
3. Paste the Worksheet_Change date macro: Paste the VBA code into the worksheet module and adjust Range("A2:A100") to your date entry range.
4. Save as a macro-enabled Excel workbook: Use Menu > Save As, select Excel Macro-Enabled Workbook (*.xlsm), and save the file to preserve the macro.
5. Enable macros and test: Reopen the file, enable macros when prompted, and type a short date such as 0312 to confirm it converts correctly.

Enter dates from short entries like 0312 without typing the yearOpens and saves Microsoft Excel .xlsm macro-enabled workbooksFree, lightweight Office suite with a familiar spreadsheet interfaceSupports VBA macros for automated date entry in WPS Spreadsheets

microsoft office alternative - wps office

Frequently Asked Questions

Why does typing 0312 sometimes become an unexpected date in Excel?

Excel passes the entry through regional date settings and may assign the current year automatically. The order of day and month depends on the system locale, so the same digits can become March 12 or December 3.

Can I make 0312 always mean March 12 instead of December 3?

Yes. In the VBA code, set d = Left(cel.Formula, 2) and m = Right(cel.Formula, 2) for month-day order, or swap the assignments for day-month order, depending on your preferred date format.

Will the Worksheet_Change macro work for 3-digit entries like 312?

Yes. Add a Case 3 branch that assigns the first digit as the day and the last two digits as the month, then uses DateSerial to create the date with the current year.

How do I keep the macro when I save the workbook?

Save the file as an Excel Macro-Enabled Workbook (*.xlsm). If you save it as a normal .xlsx file, the VBA code will not be retained.

Does this VBA date entry method work in WPS Spreadsheets?

Yes. WPS Spreadsheets supports Worksheet_Change VBA macros, so you can right-click the sheet tab, select View Code, paste the macro, save as .xlsm, and enable macros when you reopen the file.

Excel & Data Problems