# How to turn OFF Excel auto correct for dates?

**URL:** <https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399>\
**Category:** Factual Questions\
**Created:** [January 20, 2015, 6:34pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399 "2015-01-20T18:34:02Z")\
**Posts on this page:** 5\
**Page:** 2

<div class="post-metadata">

**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\
**Post date:** [August 12, 2016, 2:44pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/21 "2016-08-12T14:44:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Musicat](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/musicat/32/20189_2.png) [@Musicat](https://boards.straightdope.com/u/Musicat)\
**Post date:** [August 12, 2016, 3:56pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/22 "2016-08-12T15:56:49Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![Musicat](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/musicat/32/20189_2.png) [@Musicat](https://boards.straightdope.com/u/Musicat)\
**Post date:** [August 12, 2016, 4:32pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/24 "2016-08-12T16:32:54Z")

</div>

Reported.

---

<div class="post-metadata">

**Author:** ![pulykamell](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/pulykamell/32/3166_2.png) [@pulykamell](https://boards.straightdope.com/u/pulykamell)\
**Post date:** [August 12, 2016, 4:49pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/25 "2016-08-12T16:49:49Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![chantszhim](https://avatars.discourse-cdn.com/v4/letter/c/3bc359/32.png) [@chantszhim](https://boards.straightdope.com/u/chantszhim)\
**Post date:** [March 12, 2018, 8:45am UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/26 "2018-03-12T08:45:20Z")

</div>

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 &gt; 1 Then
        Application.EnableEvents = False
        .NumberFormat = "d/M/yyyy"
        Application.EnableEvents = True
    End If
End With

```

End Sub

[Previous page](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399.md?page=1)