Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 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.

share|improve this answer
    
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 '12 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 '12 at 20:34
    
It's nice you're trying to provide a workaround, but this doesn't really answer the OP's question. –  Kenny Evitt Jul 12 '13 at 13:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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