Small Excel annoyance

When I type "September 2008" as a column header into a cell in Excel, it decides that what I really want to say is "Sep-08". If I had wanted Sep-08, I would have typed Sep-08.

I know that I can force it to accept September 2008 by putting a leading space ahead of September, forcing the cell into text mode, and I can format the cell (or row, or column) to do what I want if I do it before I enter the data. [No, actually, "mmmmmmmmm yyyy" is not one of the accepted Date formats] But surely there is a global default that I can set to tell Excel to stop trying to second-guess my date entries. Isn't there?

I have a similar complaint about entering a mm-dd date as well. If I type in "12-16", excel changes it to "16-Dec".

Is there a way to turn this "nannying" off?

tanstaafl.

I believe (haven't tried this myself) that you can create a template file that has the cells formatted the way you like them. Then I think you can set Excel to use that template as your default when you open a blank page.

You're not the only one it seems:

http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/be333206f7725812?pli=1

Select all of the cells. (The easiest way to do this is to click in the little box at the upper left corner above the row labels and to the left of the column labels.) Right-click on the selected cells. (That is, anywhere on the sheet.) Select "Format Cells". Click on the "Number" tab. Select the "Text" category. Click "OK".

Now all of your cells are in text mode and will not reinterpret what you type into them.

It also means you won't be able to do date arithmetic.


No, but "mmmm yyyy" is and will meet your template criterion. (You have to enter this in the "Custom" category, if you don't already know that.)

Edit: I think I may have misinterpreted your request. If so, sorry.

I've had the exact same issue as your example. When I don't want Excel to auto format something for me I type in ' before the value. In this case I would have typed 'September 2008


I have to use Excel just about daily for sending and receiving data from clients. I have found that the Excel addon http://www.sobolsoft.com/excelremove/ and UltraEdit's column mode helps me get my work done in hours instead of days.

While it may be a design flaw, it's not intentionally nannying you. The issue is that when it sees something that it thinks looks like a date, it converts it to its internal date storage format, which is a number representing the number of days since January 1, 1900. (Times are represented as fractions of a day.) You can see this by entering a date and then converting the format to "General".

So what happens is that Excel sees something that looks like a date and immediately converts it to a number. But then it's lost the format you entered. So it has to default to something, which it does.

On the other hand, it clearly remembers part of the format of your entry, as it remembers whether or not you've entered a year. So it seems like something that they could fix if they wanted to. All the pieces seem to be in place.

So this could be considered a bug, or a misfeature, or a deficiency, but it's not nannying.

</pedant>

I love Ultraedit's column mode like I love the air I breathe. I could not live without it. It is the best thing to ever happen to any text editor in the entire universe.

How does the column mode compare to nedit's column editing?

And vim's column mode.. ?

Another vote for Column mode (Alt-C) in UltraEdit.
Not sure about VIM or nedit.

I'm sure it works more or less the same. I had just never used those other two editors, UltraEdit was the first editor I'd ever used that had a column mode.

To be fair, vim's "column" mode is just being able to select an arbitrarily sized rectangular block of text, like the near-useless selection mode in cmd.exe. (Unless there's something else in vim I'm unaware of.) But vi can do a lot of stuff based on a selection, far more than you might think.

Or for something even more obscure, Trix AC.

Searching Google for that brought up this about TMDS. I recall using TRIX, from a paper terminal, with a TMDS monitor being updated to display the text file, upon which column edits were being produced.

Yeah Baby! I was using a CDC 7600 for something as mundane as text editing.

Edit:(at the time it didn't seem all that big a deal)