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

 

[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