DEFAULT FORMAT PROBLEM IN EXCEL

[dbvogt]

Excel is autoformatting SKU/part numbers as dates. An application is creating Excel spreadsheets from a database and entering part numbers, some of which begin as 12-. For example 12-5901. A new spreadsheet is created but Excel automatically converts a cell with a number to Dec 1-01 (12/1/5901). I can find no startup switch to prevent this kind of autoformatting. Formatting as general after the file is created gives a totally different number. Coding in the app to search out all numbers from 1- through 12- and adding a ‘ is going to take some time. Likewise prepending a zero to the number works but changes the number – no good especially if it’s someone else’s part no. Anyone have similar problems and have a workaround?

 

[mintjulep]

You need to format the cells as general before you populate them.

If you can't do that, add an apostrophe ' to the front of your date.

'12-5901

 

[SkipVought]

Hi,

Why do Dates and Times seem to be so much trouble? faq68-5827

This is NOT a FORMATTING problem. It is a DATA problem.

Excel analyses ANY data entry as a text string. Based on various patterns, it determines if you have a NUMBER or a DATE or TIME and then converts the STRING to a NUMBER or DATE/TIME value.

Excel does not do this conversion if the cell has been previously formatted as TEXT or if the data entry value is prefixed with a single quote
[tt]
'2/15/2007 is just a TEXT string
2/15/2007 Excel parses year, month & day and converts to 39129 and FORMATS as a date.
[/tt]
Once you understand what Excel will do, you can design work-arounds.

Skip,
[sub]
[red][/red]
[/sub]