How to convert formatted date into text.

[Ks DRM]

I have imported a date field in the form of dd/mm/yyyy which i formatted
through excel to yyyy-dd-mm.

However this only affects the display and does not actually change the input
to the same format.

How I have the input converted to this format as I need to export it in csv
which only takes the input and does not carry the formatting.

 

[Pete_UK]

If you want to convert it into a text value then you can use this
formula in a helper column:

=TEXT(A1,"yyyy-dd-mm")

assuming the date is in A1. Copy the formula down, then you can fix
the values by highlighting the cells with the formula in, click <copy>
then Edit | Paste Special | Values (check) | OK then <Esc>. Now you
can copy/paste the helper cells over the original data, and then
delete the helper column.

Hope this helps.

Pete

 

[David Biddulph]

I think you'll find that the CSV will keep the format in which you've
displayed the cell when you save it. Look at the CSV with Notepad.

The problem probably arises if you subsequently try to open the CSV with
Excel. If you want to do that and retain the formatting of the CSV, use
Data/ Import External Data to read the CSV, and specify the columns as text.

 

[Ks DRM]

Thanks Peter, you are a life saver.

David, I tried saving it in CSV, but it did not save my formatted text. It
saved the original date which was in the field.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Thanks Peter, you are a life saver.

David, I tried saving it in CSV, but it did not save my formatted text. It
saved the original date which was in the field.






- Show quoted text -

 

[Dave]

Hi,
The only way I know how to do this is use Text to Columns
Highlight your date column, DATA > Text To Columns
Choose / as the delimiter
Choose a destination cell in a spare column (with 4 spare columns to the
right of it.
Press OK
The parsed data is formatted as general.
You can use the 4th spare column to Concatonate, combining them as required.
Regards - Dave

 

[David Biddulph]

So what date did you have in the cell, how was it formatted, and what did
you see when you opened the CSV with Notepad?