Adding the year in date formatting without having to type it in

[johnsboxftm]

Hello all, 

I currently have my dates typed in the column as 3-12 because the worksheet already has the year on it.  I have it set up that way so all I have to do is type in "0312" instead of "03122023" and the result is 3-12.  I want to know if it is possible to type in "0312" and the result be 3-12-2023 without having to type in 2023.

I appreciate your time and thank you.

[#2]

@johnsboxftm 

I'll assume that you use dd-mm-yyyy as date format. See below the code for the change you need to make if you use mm-dd-yyyy.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open it.

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
    ' The range in which you want to enter dates as 0312
    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 ' For example 312
                    d = Left(cel.Formula, 1)
                    m = Right(cel.Formula, 2)
                    cel.Value = DateSerial(y, m, d)
                Case 4 ' For example 1112
                    d = Left(cel.Formula, 2)
                    m = Right(cel.Formula, 2)
                    cel.Value = DateSerial(y, m, d)
                Case Else ' Invalid input
                    Application.Undo
            End Select
        End If
    Next cel
ExitHere:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Warning: if you enter a value that is not valid as a date, the result will be unexpected.

If you use mm-dd-yyyy as date format, switch the calculations of d and m, for example

                    m = Left(cel.Formula, 1)
                    d = Right(cel.Formula, 2)

 and

                    m = Left(cel.Formula, 2)
                    d = Right(cel.Formula, 2)