Excel Change Default Date Format

Dairyfarmer

Expert Member
Joined
Apr 17, 2016
Messages
3,090
Something I have yet to find out how to change over many years, with various versions of Office and Windows.

The default way of displaying dates without the need to apply formatting.

In a cell you type 21/11 and it will display 21-Nov rather than the short date format of 21/11/19 as set in the Windows regional settings. Windows settings will only change how dates are displayed when cells are formatted.

So has anyone ever got Excel to use dd/mm/yy as the default way of displaying dates or perceived dates? And I don't want to do this every time I open a new workbook either, else I just continue to use cell formatting.

**************************
12/11/19
Seahawks 9-2, 49'ers 8-1
**************************
 
Last edited:

rh1

Senior Member
Joined
Aug 5, 2011
Messages
716
Windows regional, language settings. There is setting there to change the dates display. It will pull through to excel
 

rh1

Senior Member
Joined
Aug 5, 2011
Messages
716
I will post screenshot when next I switch on a pc
 

riscbroker

Expert Member
Joined
Sep 29, 2006
Messages
1,836
If you're not performing operations against the date, you could type an apostrophe before entering the date, for example:

'21/10 or '21/10/19

The apostrophe will not be visible in the cell. You can also insert a space directly before typing the date, but the space will be visible.
 
Last edited:

rh1

Senior Member
Joined
Aug 5, 2011
Messages
716
My current date format in excel:
738853

Open Control Panel (Do not use windows 10 settings)
738855

Click on Region, and the following pop up window will appear:
738857

Click on Additional Settings, another pop up window appears:
738859

Adjust your dates formats to what you want, click apply and ok.

See below for you new date display in excel:
738861

Note it will adjust your windows date display as well:
738863
 

rh1

Senior Member
Joined
Aug 5, 2011
Messages
716
I don't think there is any other way where you can have a different default date format in excel from what you have in the windows system. You might want to create a template which the date format is customised to what you want to display.
 

Dairyfarmer

Expert Member
Joined
Apr 17, 2016
Messages
3,090
Windows regional, language settings. There is setting there to change the dates display. It will pull through to excel
Like I said it is not affected by Regional settings. I specifically mentioned that in my original post.
Entering 21/11 will display 21-Nov. Selecting date format from cell formatting will use the settings in Regional settings.
738901738903
 
Last edited:

MightyQuin

Honorary Master
Joined
Oct 6, 2010
Messages
14,098
Like I said it is not affected by Regional settings. I specifically mentioned that in my original post.
Entering 21/11 will display 21-Nov. Selecting date format from cell formatting will use the settings in Regional settings.
It's all in the fine print:

738899
 

LaraC

Honorary Master
Joined
Mar 11, 2014
Messages
37,377
You'll have to edit the normal cell style and save the workbook as the new template. :)

ps. This method will treat every cell as a date, even numbers.
 
Last edited:

Ivan Leon

Expert Member
Joined
May 27, 2008
Messages
2,678
Simply create a CUSTOM date format in Excel as follows:

1. type in the dates as per your system's regional settings option, like this:

738979

Then select all the dates you need to re-format & customise:

738983

On the HOME ribbon tab, in the NUMBER group, click on the dialog box launcher arrow at the bottom right:

738985

Select CUSTOM at the bottom left of the dialog box - the date code options will show up on the right as you have already pre-selected them:

738987

Use the following date codes to set up your custom date format, and enter them in the text box under 'Type' after deleting the default option displayed:

d for days - numbers less than 10 will display as a single digit, i.e. 1 for the 1st day
m for months - numbers less than 10 will display as a single digit, i.e. 9 for September

dd for days - numbers less than 10 will display as double digits with a leading 0, i.e. 01 for the 1st day
mm for months - numbers less than 10 will display as double digits with a leading 0, i.e. 09 for September
yy for years - numbers less than 10 will display as double digits with a leading 0, i.e. 05 for 2005

ddd - days display as 3-letter abbreviations, i.e. Mon for Monday
mmm - months display as 3-letter abbreviations, i.e. Sep for September

dddd - days will display in full - i.e. Monday
mmmm - months will display in full, i.e. September
yyyy - years will display in full, i.e. 2005

so if you need it to show it in a custom format, use the codes like this (date example is for 25 November 2019):

dd/mm/yy - date will appear as 25/11/19
dd mm yy - date will display as 25 11 19
dd-mm-yy - date will display as 25-11-19

dd mmm yy - date will display as 25 Nov 19

dd mmmm yyyy - date will display as 25 November 2019

You can juggle them around as well to make other suitable combinations as required, for example:

ddd, dd/mm/yy - date will display as Mon, 25/11/19
dddd dd/mmm/yyyy - date will display as Monday 25 Nov 2019

The 'Sample' box, above 'Type' displays how your date will display when using these date code combinations as shown above:

738991

then click OK to apply your customised date format onto your selected dates - If your column is not wide enough when applying your new date format, you will get the dates displayed like this:

738995

Double click on the column labels divider indicator between the truncated dates & the next data column to make them auto-fit the column width correctly

738997

I hope this clarifies how to reformat dates in Excel into a customised preset format.
 

rh1

Senior Member
Joined
Aug 5, 2011
Messages
716
Hi Dairyfarmer

If you change the regional settings to dd-mm it will display 09-11, if you change it to dd-mmm, it will display 09-Nov, if you change it dd mm, it will display 09 11, if you change it dd mmm, it will display 09 Nov.

Kind regards,

RH
 

LaraC

Honorary Master
Joined
Mar 11, 2014
Messages
37,377
So basically no one knows. Thanks
Changing the normal cell behaviour will be more trouble than it is worth, but if you are not ready to raise the white flag...

Ensure that your Short date format is setup the way you want in Windows Regional settings.

Change the default cell style in Excel by selecting the short date style on the format. The number format will change to the picture below.
739143
 

Dairyfarmer

Expert Member
Joined
Apr 17, 2016
Messages
3,090
Maybe I was not making it clear because everyone keeps going on about formatting in Excel and Windows regional settings.

So let me try and simplify it a be:

If you type 21/11 in a cell it will display 21-Nov *
I want it to display 21/11/19
I don't want to use cell formatting
The is no setting in Windows that will make Excel show dd/mm/yy without formatting

* If Excel can assume 21/11 is a date without any user action, why can't the default be changed to dd/mm/yy?
 

saor

Honorary Master
Joined
Feb 3, 2012
Messages
20,609
Ensure that your Short date format is setup the way you want in Windows Regional settings.
I can see why this is frustrating @Dairyfarmer. It's stupid that the way a specific program formats something (the date in Excel) is dependent on a system setting (the windows regional date format). Someone might actually prefer the date displayed differently in different apps so the date formatting should surely be specified in the app, not pulled from a system setting. Excel seriously can't define its own date formatting independent of the o/s?
 

LaraC

Honorary Master
Joined
Mar 11, 2014
Messages
37,377
Maybe I was not making it clear because everyone keeps going on about formatting in Excel and Windows regional settings.
/snip
Maybe Microsoft wanted to please the majority of the people and not an individual. :D

They did however give you the option to change the default cell style to the way that you want it. :)
 

RedViking

Nord of the South
Joined
Feb 23, 2012
Messages
21,851
So basically no one knows. Thanks
Have you changed the REGIONAL FORMAT settings for your dates in system settings? You might have to change it to English (US) or English (UK) to get the format you want. Also check the SHORT DATE format.

You can also check in excel by checking what language is use to give what format under Locale.

Format Cells > Number > Date > Locale and then change your system setting to that.


Can you do it in Exel without using styles and a default template. No.
 
Top