How to convert formatted date into text.
 

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.

How to convert formatted date into text.
 

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

On Apr 11, 7:29*pm, Ks DRM wrote:
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.

How to convert formatted date into text.
 

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.
--
David Biddulph

"Ks DRM" wrote in message
...
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.

How to convert formatted date into text.
 

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" wrote:

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

On Apr 11, 7:29 pm, Ks DRM wrote:
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.

How to convert formatted date into text.
 

You're welcome - thanks for feeding back.

Pete

On Apr 11, 9:49*pm, Ks DRM wrote:
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" wrote:
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

On Apr 11, 7:29 pm, Ks DRM wrote:
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.- Hide quoted text -

- Show quoted text -

How to convert formatted date into text.
 

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

How to convert formatted date into text.
 

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?
--
David Biddulph

"Ks DRM" wrote in message
...
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" wrote:

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

On Apr 11, 7:29 pm, Ks DRM wrote:
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.