Date Formatting

[Saxman]

Is there any way to prevent Excel from converting values to dates?

I've been down this road before, formatting worksheets to txt, selecting
txt upon importation, find '-' and replace with '.'. It doesn't always
work, especially when software creates a new worksheet.

I just wish there was a utility or a switch that I could use that could
eradicate this all together for a particular workbook.

 

[Ron Rosenfeld]

Is there any way to prevent Excel from converting values to dates?

I've been down this road before, formatting worksheets to txt, selecting
txt upon importation, find '-' and replace with '.'. It doesn't always
work, especially when software creates a new worksheet.

I just wish there was a utility or a switch that I could use that could
eradicate this all together for a particular workbook.

There is not. However, the "software that creates a new worksheet" should be able to take that into account, by formatting the cells appropriately after it creates the new worksheet; if it does not, you will need to speak with the software developer -- it's pretty basic.

One common issue is when opening .csv files. In order to format the columns in this situation, you'll need to IMPORT rather then OPEN the file. By selecting IMPORT, a wizard will appear that will enable you to format certain columns as text. Your software developer should be aware of this, as the process can be automated simply.

 

[Saxman]

There is not. However, the "software that creates a new worksheet" should be able to take that into account, by formatting the cells appropriately after it creates the new worksheet; if it does not, you will need to speak with the software developer -- it's pretty basic.

One common issue is when opening .csv files. In order to format the columns in this situation, you'll need to IMPORT rather then OPEN the file. By selecting IMPORT, a wizard will appear that will enable you to format certain columns as text. Your software developer should be aware of this, as the process can be automated simply.

Thank you Ron. Some still stick with the older versions of Excel or
order to avoid such calamities.

I have written to the software developer.

 

[Ron Rosenfeld]

Thank you Ron. Some still stick with the older versions of Excel or
order to avoid such calamities.

This "feature" has been present through many versions; probably since the first, but I cannot confirm that from personal experience. The location of the Import command changed with 2007, but it is still there.

 

[Bruce Sinclair]

This "feature" has been present through many versions; probably since the
first, but I cannot confirm that from personal experience. The location of
the Import command changed with 2007, but it is still there.

Agreed. Import is the best way to get data from csv, dat and text files
(at least) into XL in a controllable way, both for fixed width data and
data with separators in.
It's very powerful and should be more widely known.

 

[Saxman]

There is not. However, the "software that creates a new worksheet" should be able to take that into account, by formatting the cells appropriately after it creates the new worksheet; if it does not, you will need to speak with the software developer -- it's pretty basic.

One common issue is when opening .csv files. In order to format the columns in this situation, you'll need to IMPORT rather then OPEN the file. By selecting IMPORT, a wizard will appear that will enable you to format certain columns as text. Your software developer should be aware of this, as the process can be automated simply.

My merge software allows me to precede any data with a symbol of choice.
That way Excel ignores it as a date. However, after the data has been
merged and I choose to remove the chosen data (find/replace), any data
with a hyphen still converts to a date even if I choose to format the
column as text.

I will have to play around with this some more and report back.