Cannot Change Date Format in Calc

users
#1

I have an .ods file and a column with dates in it resists all my attempts to change the format. The column was created when I opened a .csv file in Calc and the dates were shown as, for example, 12 Feb 2014.
At some time during the editing of this file the dates became shown as 12-Feb-14 but I have not intended this change. What I want is date in DD/MM/YYYY format. I can highlight the column and try and format it but nothing changes. Am I doing something wrong here?
Budgie

#2

Hi Tom,
Thanks for the reply. Your explanation is what I too suspect and yes it is related to what I have been working on with Brian's help. I am not so confident however about your "reasonably easy to change the text format to a date." Do you have any suggestions?
Budgie

#3

Budgie

Try using FORMAT >> CELLS >> Numbers then select Date then select date format. This works for me when I want to clean csv file date imports.

#4

Hi :slight_smile:
Is this related to the thread being handled by Brian? If so the
problem might be that you are trying to store the date as a
text-field. It should be reasonably easy to change the text-format to
a date one though.

I think the date has to be stored as a numerical value in order to be
read in as a date. Then just applying the appropriate date format
should display the date as required.

Regards from
Tom :slight_smile:

#5

Hi :slight_smile:
That is what i was thinking of but now i re-read the original post in
this thread i think that has already been tried.

Maybe just use Brian's advice but instead of making it a text-field
just keep it as a normal number-field or (if it does allow you (which
i doubt)) as a date field. In the Csv file the date then probably
won't look like a date, just as some weird number instead. Calc will
let you format the column in some suitable date format.
Regards from
Tom :slight_smile:

#6

Hi Brian and Jay,
Well your analysis is correct and the "date" is in text format. What is odd however is that the format changed from 12 Feb 2014 which DATEVALUE can recognise and I could have used to 12-Feb-2014 which it cannot. Since there are only 40 odd lines involved I have entered the dates correctly by hand. Next time I shall operate on initial file with DATEVALUE.
Thanks again for your help.
Budgie

#7

The obvious problem that would prevent your changing the format is that you actually have text in these cells, not dates proper - though it's difficult to see how the unintentional change could then have happened. If that's the case, there are two probable solutions:

1. Go back to the CSV file and select "DATE (DMY)" as the "Column type" - so you get dates instead of text.

2. If you've done too much work for that to be desirable:
o Create a new column with =DATEVALUE(Xn) - with the Xn being the existing text date value. Don't worry if the new values look funny.
o Copy the new date values back over the originals, but using Paste Special with Formulas *not* ticked.
o Now format the new date values as desired.

Oh, but I see you've beaten me to this ...

Brian Barker

#8

For what it's worth, DATEVALUE() can certainly cope with that format (of text value) for me. But it won't cope with a genuine date formatted in that way, of course. It sounds as if at least some of your dates may really have been dates after all. There's no guarantee that all values in a column are of the same type or format, of course.

Brian Barker