11

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.

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
3
  • 5
    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.
    – RossFabricant
    Dec 7, 2012 at 17:21
  • @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.
    – Pynner
    Dec 9, 2012 at 20:34
  • 4
    It's nice you're trying to provide a workaround, but this doesn't really answer the OP's question.
    – Kenny Evitt
    Jul 12, 2013 at 13:19
1

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".
Improve this answer
1
  • 1
    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.
    – melds
    Dec 30, 2020 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.