Microsoft Excel annoyance #1532

How do I prevent Excel from deciding it knows better then me? In this case, how do I prevent it from reformatting dates without asking or even indicating it has done this?

Situation: attached is a .tab file, similar to one that Excel automunges. Open it in a text editor, and dates appear as 2005-8-15 as they should be. Open the file in Excel, and the same date appears as 8/15/2005. Then when the file is saved, 8/15/2005 is written to it.

I've looked at the hundreds of options Excel has and can't find what does this, and a google search leads me to a ton of sites on how to format dates in proper .xls spreadsheets, but none talking about this type of automunging of non Excel formatted files.

Are the cells formatted as date, and have you adjusted the display characteristics accordingly?

Edit: Got it. See attached. You have to set the data type as custom, and use the format as "yyyy-m-d".

Put a ' in front of the date is the best way I know of preventing Excel from Auto Correcting. The other method I know of requires that you use open file wizard and set the column that End Date uses to Text instead of General.

If you don't like those you could just change the date format for the column after you open it. Right click on column C -> Format Cells -> Number tab pick Custom. In the Type: field enter YYYY-M-D

Hmm, neither of these solutions really works well in the enviornment this file comes from. It is opened out of a source control program and manually opening the file via the open file wizard would be time consuming, since the true path to the file is quite burried in a ton of folders.

The actual data has to stay 2005-8-15, as adding quotes to it would break the program reading the file just as much as the automunging currently does.

Changing the format on the cell is also time consuming, since it would have to be done every time the file is opened. Storing it as anything except a .tab text formatted file is not possible, as it has to be in that format to later be read by the program that uses it.

The current workaround right now is to edit the file in Excel, then save and open the tile in a text editor and manually fix the dates. Working on it only in the text editor is a nightmare because the real file this came from is much larger and with many more columns. However, if someone forgets to fix the dates, the program crashes on startup because the data is invalid.

Perhaps an alternative spreadsheet app would serve you better? Then you could just re-register the .tab extension to open in that app.

Maybe Column Mode in UltraEdit would be a better tool for editting the file.

The only way that I know of to do what you want is to tell Excel in advance to expect text. You can do this by invoking the import wizard or assigning the following code to an open button in your general macros. Use this to open your tab files instead of the File Open option.

Sub TabOpen()
NewFN = Application.GetOpenFilename(FileFilter:="Tom's Files (*.tab), *.tab", Title:="Please select a file")
If NewFN = False Then
Exit Sub
Else
Workbooks.OpenText Filename:= _
NewFN, Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
Array(3, 2)), TrailingMinusNumbers:=True

End If
End Sub

As implied, Excel is recognizing the field as a date field and is autoconverting it to store it as a date internally, and then putting it in the default date format.

The first thing that comes to mind is seeing if you can change Excel's default date format to match what you want. I don't know if there's any such feature, but it might be in some internationalization options.

The second thing that comes to mind is "why are you using Excel?" It sounds like you've got an automated process (since you imply that these files are not being opened in Excel by hand). Maybe you could find a different tool than Excel to do whatever it is that's being done to the file. If you could fill us in on that part, maybe we could help figuring out what that tool might be.

Essentially, this is a datafile for the game. A designer does manually edit the file to add something to it, then it gets converted to a binary database format by the build system. Every other file like it works fine when edited by Excel since they don't have dates, but this one file causes a designer grief nearly every time.

For the tools we have already installed on systems, Excel works the best for editing them. I doubt the cost of a product like UltraEdit could be justified when what we have should work fine. I should have known Office would yet again do something it thinks is helpful, and offer no way to turn it off.

Could it be because Windows is set to m/d/yyyy as a date-format? Maybe try to set Windows to yyyy-m-d?

Stig