Controlling Automatic Formatting of Dates

@Joe Fuller:
Thank you for your explanation, I can see why you would want to do this in such specific situation.
For just macros, I organize them in Personal.xlsb by grouping related macros in different modules, appropriately named, so I can easily find all related macros. But as you, I have my style of working with Excel that wouldn't suit everyone. For very specific workbooks, I keep macros and other information in them, but use those as templates rather than what you propose. That helps me, for example, keep my chemical process balances not interfering with my financial spreadsheets, or Data for Navigation Charts updates.

@J. Woolley:
Nice, albeit advanced, trick on triggering a procedure stored in Personal Macro Book. Another way to achieve this is to have an event-triggered procedure for a particular sheet that would call the Personal-Macro procedure; that way you would have full control of which sheets would call that procedure, rather than have every sheet executing it.
My question to @Joe Fuller was to find if there are any benefits from having one more workbook with macros rather than just keeping them in Personal.xlsb and he did answer that question.
My procedure in the second comment was not event-triggered and could be workbook specific, or general kept in Personal.xlsb.

@Joe Fuller and Tomek
Sorry guys, but an event procedure like the Tip's Worksheet_Change (or even Workbook_SheetChange) only applies to the worksheet (or workbook) that owns the code; therefore, it doesn't help to put it in another open workbook or in PERSONAL.xlsb.
But here is a way to create an event procedure that DOES apply to external workbooks. The following assumes PERSONAL.xlsb, but it also works with Joe Fuller's always open workbook or with an add-in like MyToolbox.xlam:
1. Press Alt+F11 to open Visual Basic Editor (VBE).
2. Select PERSONAL.xlsb's ThisWorkbook module and press F7 to View Code.
3. Copy and Paste the following code at the beginning of ThisWorkbook:

Private WithEvents MyApp As Application
Private Sub Workbook_Open()
    Set MyApp = Application
End Sub
Private Sub MyApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If IsDate(Target) Then
        Target.NumberFormat = "m""/""d""/""yyyy"
    End If
End Sub

4. Press Ctrl+S to save the code before leaving VBE.
Notice NumberFormat is "m""/""d""/""yyyy" to avoid automatic conversion to Short Date as explained by Tomek.
The MyApp_SheetChange event procedure will run every time a cell is changed on any open workbook. This is like swatting flies with a sledge-hammer. Tomek's macro might be more appropriate. Or as suggested by the Tip, "simply format the cells to the Date format" you prefer.
See http://www.cpearson.com/Excel/AppEvent.aspx

Tomek: In answer to your question "How is it different from having the macros in your Personal Macro File PERSONAL.xlsb, which is also always opened when you start Excel?", it probably isn't for anyone that simply wants to call up a macro or several macros, but I use that particular .xlsm file for several things other than for just macros. For me, it's also handy having it always readily available as an extra tab (I use the Office Tab add in) for reference material and also quickly adding / changing macros. I take your point though: how I use that file is not an average user case by any stretch.

Alternatively you can have a similar macro assigned to a keyboard shortcut. For example:

Sub DateEntered()

' Keyboard Shortcut: Ctrl+Shift+D

Selection.NumberFormat = "mm/dd/yyyy"
End Sub

That would enable you to use this shortcut on a cell or range of cells into which you are going to enter the date(s), but also format all selected cells with dates already netered to "correct" their date format. This allows you to enter dates in other cells and leave them in different format, unlike the Change event macro from the tip, which will convert any entered date.

On a tangential note: on my computer (region is Canada), the default date separator is a hyphen, so all "/" in the format will be automatically changed into "-". In such case, and to have full control of the separator, I must enter the format as "MM""/""dd""/""yyyy".
Also, the format "m/d/yyyy" is interpreted as short date and then the date entered on my system is displayed as 2023-11-05 (which is the default short date) rather then the requested 11/5/2023.

Joe Fuller: How is it different from having the macros in your Personal Macro File PERSONAL.xlsb, which is also always opened when you start Excel?

I'm unsure if the following will be of any assistance to Ted, but I thought I'd share how I use all my macros with around 50 different xlsx or csv worksheets I work on each day.

I have well over 200 macros many of which are set up as macro icons on customised tabs as well as the Quick Access Toolbar. Those macros are all saved in one xlsm file, which is always open in Excel. As the macro icons are set up based on that particular xlsm file, even I forget to open it and click on a macro icon, Excel will ask if I want to enable that xlsm file.

So, in short, by having that xlsm file open when working on any other file, those 200 plus macros are always available to me.