At 10:04 22/09/2010 -0700, Brewster Gillett wrote:
Here's that dadblasted now-you-see-it-now-you-don't "text indicator"
(I seem to recall that's what someone once called it) that I have
got to eradicate.
Don't think of it as a problem: it's there to help!
[...] Unfortunately the way I get the file, as a CSV, those entries
are not formatted specifically as dates, even though they all follow
a common format of MM/DD/YYYY. I note that each entry is preceded
*in the edit window*, but *not* in the cell itself, by a single
quote. I believe this single quote is a forced text format
identifier of some sort.
It's just there to tell you that what may look like a date (a variety
of number) is not: instead, it is actually a string of text characters.
I have highlighted that column and (1) tried "FORMAT CELLS" to force
it to "DATE" format - no luck
The only thing you can display as a date is a number (representing
the number of days since the reference date).
(2) invoked Search and Replace to replace the single quote with null
- also no luck - S&R can't *see* the danged single quote, from all appearances.
That's because it doesn't exist in the cell - it's only in the Input
Line to help you.
I have performed both of the above attempts on the file when in its
original .CSV format, and also in its versions that have been saved
as .ODS files.
Whichever format you are choosing to save files, the behaviour whilst
the file is open will be much the same.
At 12:34 22/09/2010 -0700, Brewster Gillett wrote:
The other question is, how can it be that you can highlight
("SELECT") an entire column, go into "FORMAT, CELLS" where it gives
you at least two date options, and the option selection is ignored
WRT the selected cells?
Only numbers can be dates. Any numeric values in your cells will
change to show the date format you have selected. Text values -
however much they might look like dates - cannot change. (Don't be
confused by the fact that if you type something similar into a cell,
it may well be automatically converted to a date.)
I can go back to the "FORMAT" dropdown, and the specific form of
date that I selected earlier is still highlighted, but when I
attempt to perform that operation on even just one cell, it ignores
the selection - is it that single-quote that's binding things up?
No. But it is the fact that your values are text - which is
precisely what the single quote mark is warning you.
At 22:51 22/09/2010 -0700, Brewster Gillett wrote:
Gene Young wrote:
Try substituting the actual cell reference in place of
"THEDATECELL" eg; =DATEVALUE($E5) where cell E5 is the cell that
contains the date.
So I tried it as you describe.
Got a "522" error msg. ("iteration", it says - and even after
reading the description I am not certain how it applies to this case.)
It sounds as if you have tried to put the formula into the same cell
as the data! That means the input to the formula needs to be its own
result. You have created a recursive formula.
You will have seen Bob Long's solution - which is by far the
best. You say you are deriving this information from a CSV
file. All you need to do is to select "Date (MDY)" for Column type
in the Text Import dialogue. Your dates will then appear as dates
(and can be formatted as you wish). As he also suggests, even if you
no longer have access to the CSV file, you can save your data from
Calc as a CSV file and then use the same technique whilst re-importing it.
But if you want a bit of fun, here's another idea. Enter
=DATE(VALUE(RIGHT(C2;4));VALUE(LEFT(C2;FIND("/";C2)-1));VALUE(MID(C2;FIND("/";C2)+1;FIND("/";C2;FIND("/";C2)+1)-FIND("/";C2)-1)))
into D2 and fill it down the column. The FINDs search your text
strings for the slashes and the LEFT, MID, and RIGHT separate the
three parts of the date; VALUE converts each part from text to a
number and DATE reassembles these into a numeric date value. The
result is the date values you require.
Oh, one last point: it sometimes helps in situations such as this to
go to View | Value Highlighting (or Ctrl+F8). This shows text values
in black but numeric values (including real dates) in blue (and the
results of formulae in green).
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
