Harold Fuchs wrote:
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.
Thank you for the solution. I will be able to use Ely's approach.
Cheers,
Kyle
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
