Excel Default Date Format When Not Including The Year In The Entry

  • Thread starter Ronald R. Dodge, Jr.
  • Start date

Ronald R. Dodge, Jr.

When not including the year into the entry as you know Excel automatically
put the current year as the year for the date, it also formats the cell as
d-mmm when the cell was previously set as a "General" format. Regional
settings has been set to the format of mm/dd/yyyy, but that doesn't have any
impact on the format used by Excel in this case.

I have seen articles suggesting to use either a template (but that means all
cells are formatted as date, which that isn't good either for other reasons)
for creating new workbooks, or to use a macro within "ThisWorkbook" class
module. But then that means it's being triggered every time some little
change takes place within the workbook. Just like with the auto calculate,
it eats up processing time and can slow down the person who is working on
the file, especially if that person is doing data entry. Not only that, but
unlike calculations can be switched over to manual, there is no real way to
turn this macro thing off without either going into design mode via the
Visual Basic Editor toolbar which is disabling macros, or one would have to
go directly into the VBE to cause the event to bypass the set of procedures
via either a GOTO statement, End Sub statement, or use and If, Then, Else
structure relying on some value to determine rather if to go through it or
not, which none of these options makes any sense to use anyhow for this
situation as that would take up even more processing time than to just leave
that original macro in place.

Most people who I work with also don't use this default date format of
d-mmm, but rather many of them use m/d/yy. I don't care for that format
either for 2 reasons. First, it doesn't show the full year, and secondly,
with single number months and/or days of the month, one would have to be
second guessing the width of the column, which by using the format of
mm/dd/yyyy, it addresses both issues.

Is there an actual place within Excel to change this default behavior?


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000


Have you tried Custom Format ?
Select the cells that you require the format then:
Format Cells > Number, select Custom and enter in Type: mm/dd/yyyy > OK
Hope this helps

Terence Lam

I think the original poster meant very clearly that he wants to know HOW TO

Formatting works, but then you need to do it everytime.

We want to change the default setting.

Any taker?

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question