Excel Change Default Date Format

Dairyfarmer

Dairyfarmer

Executive Member
Joined
Apr 17, 2016
Messages
6,210
Reaction score
3,882
Location
Tiocfaidh ár lá
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:
Windows regional, language settings. There is setting there to change the dates display. It will pull through to excel
 
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:
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
 
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.
 
rh1 said:
Windows regional, language settings. There is setting there to change the dates display. It will pull through to excel
Click to expand...
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:
Dairyfarmer said:
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.
Click to expand...
It's all in the fine print:

738899
 
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:
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.
 
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
 
Dairyfarmer said:
So basically no one knows. Thanks
Click to expand...
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
 
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?
 
LaraC said:
Ensure that your Short date format is setup the way you want in Windows Regional settings.
Click to expand...
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?
 
Dairyfarmer said:
Maybe I was not making it clear because everyone keeps going on about formatting in Excel and Windows regional settings.
/snip
Click to expand...
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. :)
 
Dairyfarmer said:
So basically no one knows. Thanks
Click to expand...

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.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X