Excel/Windows: Change default date display format

By default, the “short date” display format for Windows (and thus Excel) is “M/d/yyyy.” For example, June 1, 2011 would be displayed as 6/1/2011. For varying reasons, you may want to zero-fill your dates and have June 1, 2011 read as 06/01/2011. Doing this actually involves a Windows setting, not an Excel setting. So, be prepared to have your dates displayed accordingly across the whole operating system and many other programs.

To change the default date format in Windows:

Go to Start –> Control Panel and select “Regional Settings” or “Region and Language,” depending on your operating system.
In the Short date format list, select the desired format:
Click OK.

Now, unless the dates in Excel were formatted using the format menu, they should now display as you’ve defined them.

This entry was posted on June 1, 2011, 8:32 pm and is filed under Excel, General, MS Office, Windows 7, Windows Vista. You can follow any responses to this entry through RSS 2.0. You can leave a response, or trackback from your own site.

#1 by dennis brannon on June 1, 2011 - 9:32 pm

If I make this entry in a cell: 1/15/11 I end up with the desired result. (note: I’m not so worried about the zero-fill as I might have indicated)

But if I make this entry in a cell: 1/15 I end up with “1-Jan”

This would indicate the default date setting is dd-mmm
Yes?
I’ve still not met anyone that really digs that default format. Most would prefer mm/dd/yy, mm/dd/yyyy, or even dd/mm/yy. But dd-mmm???
That one’s a puzzle to me.

So my intention would be to establish a default date setting of mm/dd/yyyy or mm-dd-yyyy.

Thank you for your help with this.

#2 by dennis brannon on June 1, 2011 - 9:34 pm

my bad – 1/15 entry in a cell yields 15-Jan
😦

- #3 by Tim Gavin, CPA on June 1, 2011 - 9:54 pm
  
  Rats! I was hoping the easy answer was it. I have also been plagued by the problem you describe, and in all my years of searching I have not found a reasonable solution.
  
  I have heard complaints about the both the chosen format of “dd-mmm” and the fact that the automatic conversion of a number takes place at all. I have entered account numbers such as “01-1000, 01-1500, 01-2000” and end up with “01-1000, 01-1500, Jan-00.” This is excruciating and I feel your pain.
  
  If you or anyone else comes up with a solution, please share with the rest of the class!
  
  - #4 by Dennis Brannon on May 22, 2013 - 9:21 am
    
    Just checking in Tim. I’m still fumbling around with the 15-Jan formatting. FYI. (Hadn’t visited this thread in awhile, so thought I’d post where I’m at – i.e. same spot!)
#5 by dennis brannon on June 1, 2011 - 10:01 pm

In the prehistoric era (i.e. 15 years ago), Lotus users changed Lotus’ “global default” and moved on.

I suspect we’ve simply missed the logic as to why Windows prefers dd-mmm. Once we figure that out, then we’ll know where to fix the default. When we see 15-Jan, it sure looks a bunch like a date-formatting issue – but this could be a red herring.

Still stumped – after all these years….

Great post. Much appreciated.

#6 by Joanna DeVlamynck on December 31, 2012 - 9:06 am

Beware Canadians! You must set to English (USA) to use dd-mmm. For some reason this format is not allowed in the Canadian English setting. Go figure, eh?

#7 by http://thedesignstudioinc.weebly.com/ on February 8, 2013 - 7:09 pm

I desire to save this blog, “Excel/Windows: Change default date display format �
CPA Tech Tips” on my personal internet site.
Will you care if I actuallydo it? Regards ,Janine

- #8 by Dennis Brannon on May 22, 2013 - 9:22 am
  
  Any wisdom to share with us Janine?
  
#9 by Dorine on July 1, 2013 - 5:33 pm

Thanks for using some time in order to create “Excel/Windows: Change default date display format | CPA Tech
Tips”. Thanks a ton yet again ,Nelle

#10 by Mirta on August 3, 2013 - 1:36 am

I was browsing for techniques for my website and discovered ur posting,
“Excel/Windows: Change default date display format | CPA
Tech Tips”, do you really mind if perhaps I actually implement
some of your ideas? Thanks a lot -Sonya

#11 by Clifford on August 8, 2013 - 9:33 pm

I personally wonder why you branded this specific post, “Excel/Windows: Change default date display format |
CPA Tech Tips”. Anyway I admired the blog!Thanks for the post,
Candy

#12 by Keith on August 16, 2013 - 5:39 pm

I personally intend to save this specific article, “Excel/Windows:
Change default date display format | CPA Tech Tips” on my personal blog.
Do you mind in case I actuallydo? Thank you -Pearl

#13 by barry on January 7, 2014 - 3:07 am

I’ve changed my default in the regional settings to DDD/DD/MMM – Tue-07-Jan – but when I go back into Excel it still defaults to 07-Jan. Any ideas?

#14 by Sam on April 23, 2014 - 11:13 am

Thank you. This information was helpful to me.

#15 by meble łazienkowe na indywidualne zlecenie on April 25, 2014 - 3:49 am

It is perfect time to make some plans for the long run and it is time to be happy.
I have learn this post and if I could I desire to counsel
you some interesting issues or advice. Perhaps you could write subsequent articles referring
to this article. I desire to learn more things about
it!

#16 by Pam Spears on May 2, 2014 - 2:06 pm

Check out this link:
http://social.technet.microsoft.com/Forums/en-US/12f19382-aa24-4a2b-9b82-451bd202efd7/excel-2010-default-date-format-how-to-set-it?forum=excel

This solved the default date format issue in Excel for me. Now when I type ‘5/2’ I get ‘5/2/14’ in the cell, not ‘5-May’.