I have moved all my blogs to my new website: https://helpfulofficetips.wordpress.com/2014/06/03/setting-excels-default-date-format-to-two-digit-aka-mm-dd-yy/
From now on, all updates will be at the new site, and all links will take you
directly to that site. Please check it out!
I like using two-digit date formats on all my spreadsheets. This way all the dates in columns will line up, more or less. If you use a fixed font (like Courier) instead of a proportional font (like Arial), they will line up exactly but look ugly. Your choice.
Let’s use a visual.
Let’s put January 1, 2014, and October 5, 2014, and December 12, 2014, in some columns using
different styles of date format. Date format
is defined by a placeholder in most programs.
“M” is month, “d” is day, and “y” is year. (“h” is hour, and small “m” is minute, but
that’s getting too detailed.) The top row
shows the format as you explain it to your computer:
Date
|
M/d/yy
|
M/d/yyyy
|
MM/dd/yyyy
|
MM/dd/yy
|
January 1, 2014
|
1/1/14
|
1/1/2014
|
01/01/2014
|
01/01/14
|
October 5, 2014
|
10/5/14
|
10/5/2014
|
10/05/2014
|
10/05/14
|
December 12, 2014
|
12/12/14
|
12/12/2014
|
12/12/2014
|
12/12/14
|
(I’ve left aligned the
“Date” column, and right-aligned the others to make it look nice.)
Which date formats line up?
MM/dd/yyyy and MM/dd/yy
Which date formats give you the information you want in the
least amount of space?
M/d/yy and MM/dd/yy
Which date format is lines up and takes up the least amount
of space?
MM/dd/yy
You can set the format to this style in each Workbook, but
clicking the range you want to format, then
select the more choices arrow in the Home tab>Number section.
In the Format Cells window>Category section, click on
Date. In the Type choice, select the
appropriate format, as a reference Microsoft uses “03/14/12.” Notice there is a small “Sample” window where
it shows the “mm/dd/yy” format. (Usually
your computer just knows if you mean “m” month or “m” minute, so M and m are
interchangeable in most programs, but not all.
Hmmm…. M&Ms sound good right now.)
This works great for individual spreadsheets, but I want to
set up Excel so it will always default to this format on my computer. (Warning:
when you customize the default format on YOUR computer, it only changes
the default format on YOUR computer.
When you open the spreadsheet on another computer, it will use that
computer’s default format. The
formatting is not saved in the spreadsheet.)
For this, you need to change the default date format for all
Microsoft software, not just Excel. And,
of course, why would Microsoft make this easy and put in the Options for one of
the programs? Because they are
Microsoft. You must go to the Control
Panel (Start menu>Control Panel in Windows 7 or hold down the Windows key and X>Control Panel in
Windows 8.1). Now click on Clock,
Language, and Region>Region>Change date, time, or number formats. (I bet that was the first thing you tried, right?)
You will now have the Region window>Formats tab showing. Click on the drop-down arrow for short date and select “MM/dd/yy” and then click Apply. Now whenever you open a spreadsheet on THIS computer, the default short date format is two-digit month, two-digit day, two-digit year.
Aren’t you glad you only have to do it once? That is, unless you start using another computer. In fact, I am writing this blog because I just got a new computer and I forgot how to do it. If I forget next time, I'll just look it up here.
