Thread: unwanted conversion to dates (Excel 97 SR2)

1. unwanted conversion to dates (Excel 97 SR2)

Is there any way to call off Excel's logic that converts numbers that "look like dates" into dates?
I need to do a significant amount of work with part numbers of the form XX-XXXX and with dimensions in fractional values. Frequently I end up cutting and pasting these from other data sources or using formulas to create values.
Excel insists on converting results like 9/16 or 10-2348 into date numbers which changes the data.

2. Re: unwanted conversion to dates (Excel 97 SR2)

Unless you need to use the nimbers in calculations, you could prefix them with the single quote mark ( ' ) - which will not display, or perhaps format the cells as text.

Andrew C

3. Re: unwanted conversion to dates (Excel 97 SR2)

Unfortunately the most annoying times that this comes up is when I am trying to do calculations that involve those ancient "English" units known as Inches. My present project is cataloging parts to make it easier for designers not to create duplicate parts and eliminate some that we already have from our system.

One of the calculation I waned to make was how far a listed dimension is away from nominal dimensions that are even 16ths of an inch. To do this, I planned to use the fact that TEXT(A2,"# ??/16") will give a text string that is rounded to the nearest 16th.

If you put 0.137 in cell A1 and =VALUE(TEXT(A2,"# ??/16"))-A1 in cell A2, it should round the value and subtract the original value to show the difference. It works for some numbers but not those that It decides are dates or invalid dates.

I found a way around it for the particular data I was working with this morning (multiply by 16, round to 0 decimal digits, divide by 16, and subtract) but the automatic date conversion keeps hitting us when we don't expect it.

The part numbers (XX-XXXX) work well when the cells are preformatted as text unless we are pasting the data in from other software packages. When pasting the numbers, it converts them to dates. I have been sorting the input data and using the formula =(TEXT(A1,"m-yyyy")) to convert it into the proper text and then using paste special - values to over write the incorrect date number.

This is why I am looking for a way to turn off the automatic conversion. It requires a lot of manual effort whenever the effects are seen.

4. Re: unwanted conversion to dates (Excel 97 SR2)

For your "inches" situation:

Are you pasting things from other applications that look like decimals (e.g. "0.137") or that look like fractions (e.g. "1 3/16")? Can you give a specific example of what you attempt to paste that is being interpreted as a date? Can you give a specific example of what you would like to do with it, assuming it retains the format you wish? Thanks!

5. Re: unwanted conversion to dates (Excel 97 SR2)

First, format cells in your worksheet into Numbers (Format | Cells, Numbers, under Category choose Number instead of General, don't forget to choose appropriate amount of Decimal Places, and click OK). Then, when you paste your data, use command "Paste Special" (Edit | Paste Special and check Values) instead of Paste. You can write a simple macro to change Ctrl-V hotkey from "Paste" to "Paste Special" . (See the latest "Woody's Office Watch" for details.)
It will save you a lot of time to recalculating values as 2 3/16 to decimals (2.1875).

6. Re: unwanted conversion to dates (Excel 97 SR2)

Pasting things that look like decimals is no problem.
Fractions are one of the problems. "part numbers" of the form XX-YYYY are the other. Whenever XX is between 1 and 12, the number gets converted to a date.

Unfortunately, Excel seems to watch over the cell data whenever it is touched and will convert it any time it might look like a date, even if it is as an intermediate value from a calculation and not the value being placed into a cell.

As an example from this morning. I had a series of descriptions like "7/16 yellow & zinc". As soon as I erased the " yellow & zinc", the 7/16 became "7/16/2001". (the deletions were done with the search and replace function.)

This then requires me to go through all the data looking for cells with the BAD DATA that Excel created and manually correct them.

7. Re: unwanted conversion to dates (Excel 97 SR2)

If you format the cells as text and click on the cell and paste directly into the cell, XL will convert xx-yyyy to a date. However, if you click on the cell to select it, then click in the formula bar and paste the value into the formula bar and then press enter, it will not be ocnverted.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•