Setting the default date format in Excel 2013

Hi, I'd like to set the default date format for all Excel files to be dd/mm/yy.  When I type in a date it (eg 24/9) it displays as 24-Sep. 

My regional settings are English (United Kingdom) and the location is UK.  The short date is set to dd/mm/yyyy.

I know I can change this per cell/group of cells, but I'd like to change it so the default for all new sheets is dd/mm/yy


TIA,  Ben.

When you type 24/9 or 24 sep or 24sep, then excel assumes that user wants to appear date as d/mmm so by default it convert it in custom format.
But in formula bar you will observe that its showing it as 24/9/2013 or 24/9/13 depend on what settings are in control panel.

So default is dd/mm/yyyy only, as you are typing half date, its taking in that way. For changing the format of dates in whole sheet/workbook you may do it by Ctrl+H (control and replace).

In Find, you may give the formatting d/mmm which you may also select from a cell and in replace you may give dd/mm/yyyy, this also can be selected from a cell.
Then replace all.

3 people were helped by this reply


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.


Question Info

Last updated June 21, 2020 Views 38,518 Applies to: