On 16/10/2008 15:45, Kyle Nitzsche wrote:
I have thousands of cells whose actual content is something like this:
"3.23.01".
Calf insists on seeing this as a Date and displaying it in whatever
date format I have.
If I set the cell format to Text, it now displays it as a number:
36973 (probably the number of days since some time or other.)
I want and need to see the actual text: "3.23.01". (It is not a date,
in fact is is a software version.)
So, how to turn off Calc's automatic treatment of date-like numbers as
date and just show me the actual text?
Many thanks in advance,
Kyle
Assume the "dates" are in column A and that column B is spare. In cell
B1 enter the formula
=TEXT(DAY(A1);"##") & "." & TEXT(MONTH(A1);"##") & "." &
TEXT(YEAR(A1)-2000;"##")
Now drag/copy the formula as far down column B as you need.
This will make the sheet look like this:
A B
23.04.09 23.4.9
25.12.08 25.12.8
27.02.19 27.2.19
01.01.01 1.1.1
etc.
Calc knows, from our Locale setting, that we Europeans use DDMMYY. If
you use MMDDYY, you'll probably need to switch the DAY and MONTH pieces
within the formula. Note that all leading zeros will be suppressed; I
don't know how to avoid that to achieve, for example, 23.04.9 instead of
23.4.9. If you like you can now "hide" the "date" column (A in our example).
A better way is to adopt Ely Schoenfeld's
([EMAIL PROTECTED]) suggestion to format the column
"properly" as you open the .csv file but the above will "convert"
existing .ods files.
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
