Setting Excel's Default Date Format to Two-Digit, aka, mm/dd/yy

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.