How to turn OFF Excel auto correct for dates?

[don_t_ask]

At work we run hundreds of automated jobs that extract data using various varieties of SQL and pump the data into Excel spreadsheets for distribution to the relevant work areas. For any that require particular formatting we simply create a template for the report. Only has to be done once and for all eternity every subsequent report looks the same.

[Musicat]

@pulykamell

That’s what I’ve always done, and never really thought much about it, but Keeve does have a point as to why you can’t set a date formatting default preference so you don’t have to do this every time.

Here’s a workaround: Create a new file. Set the defaults as you wish, and save the file. When you start a new project, open this file and save it under your new project’s name. Repeat as needed.

[Musicat]

Reported.

[pulykamell]

@Musicat

Here’s a workaround: Create a new file. Set the defaults as you wish, and save the file. When you start a new project, open this file and save it under your new project’s name. Repeat as needed.

It’s never been something that bugged me, but I can see how another user might find it odd.

[chantszhim]

Format the column as Text, then use the following VBA

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim vCount
If Target.Cells.Count > 1 Then Exit Sub

With Target
    vCount = Len(.Value) - Len(Replace(.Value, "/", ""))

    If vCount > 1 Then
        Application.EnableEvents = False
        .NumberFormat = "d/M/yyyy"
        Application.EnableEvents = True
    End If
End With

End Sub