Change DATE Column to TEXT Column

[TC2000]

How do I Change DATE Column to TEXT Column?

I have a date column with value "02/04/2001".
Using workbooks.saveas function to save worksheet as csv, it become "2/4/2001"

Best regards

 

[DaleWatson123321]

TC2000,

This is one "workaround"... which will leave you with a "space" character in front of your numbers when you save the file as a CSV file. If you can live with the "space" character, then the following will work for you.

1) In an adjacent column, use the following formula, and copy it down for each date in your column. Naturally replace the "A1" in the formula for your date column.

=" "&RIGHT("0"&MONTH(A1),2)&"/"&RIGHT("0"&DAY(A1),2)&"/"&YEAR(A1)

2) Convert the formula column to values, and

3) Replace the date column data with your new values.

Hope this helps. Please advise as to how useful this workaround is.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[TC2000]

Dale Watson

Do you have any other suggustion for converting the date without the leading BLANK
Thanks

Regards
Tony

 

[DaleWatson123321]

Tony,

In experimenting with options, I've found that there NEEDS to be some kind of character placed ahead of the date - to keep it as "TEXT" - otherwise Excel converts it to a DATE.

The character some use is the ' ... the left-aligned label prefix. Therefore, you would use it in place of the SPACE character ...as follows:

="'"&RIGHT("0"&MONTH(A1),2)&"/"&RIGHT("0"&DAY(A1),2)&"/"&YEAR(A1)


That's the best I can come up with. I hope it's a workable alternative.

Or, perhaps someone else out there has an other option ? ? ?

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Sawatzky]

Dear Tony,

in an adjactant column try this

=TEXT(B1;"tt.MM.jj&quot
where "tt.MM.jj" should/could/may be on an english maschine "dd.MM.yy"
and B1 is the cell with the date value.


let me know if it works

regards Astrid