How can I change the default date format?

Question

When I paste a date into Excel 2010 in the format "2012-12-07 00:00:00.000" I'd like it to be displayed by default as "2012-12-07", but it is actually displayed as "00:00.0".

I know I can work around this by prepending a ' or changing the cell format, but I'm interested in changing the default format.

An approach that almost works is going to Control Panel->Region and Language->Long Time format and changing it to something like "h". This results in Excel displaying the dates I paste in as "2012-12-07 00:00:00.000", but screws up times displayed outside of Excel.

The dates I'm pasting in are from SQL Server.

Solution 1

By default Excel starts all new workbooks without formatting. This means you have to tell Excel how you want the data formatted.

To achieve what you ask, you will need to format the column to the date format you want then save the file. You have to options to save the file:

1. If you do not want this formatting applied to ALL new workbooks, save the formatted workbook as a template (xltx) or regular file. Each time you use this file it will be formatted for you to paste the data.

2. If you want it to be the starting point for ALL new workbooks, then save the template as Book.xltx in the XLSTART folder. This folder is typically located at C:Program Files\Microsoft Office\Office14\XLSTART.

Now you have a formatted file to use. When you open the file and paste the data, paste only the values and it will format it the way you've chosen.

Solution 2

It completely depends on your system date and time format:

1. Go to Control Panel.
2. Click on "Region and Language".
3. Click on "change date, time and number format".
4. Set "short date", "long date", "short time" and "long time".

Comments

[RossFabricant]

A column using the date format won't show some non-date values correctly, so I don't see how this approach could work. EG, if I set column A to always use date format my dates would show up correctly but not numbers. And dates pasted into column B would still be displayed incorrectly.

[Pynner]

@RossFabricant you are correct, however excel's default formatting for all cells is general, which means the excel will guess the data type based on what you enter. I don't know of any interface in excel that allows these guesses to be customized.

[melds]

The system date and time format only affects how the data is interpreted when it's added. The display format is from within Excel. The problem is that the General format doesn't display a reasonable default and needs to be set for each cell. OP would benefit from changing the default for the General format, but that's not possible.