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

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)