Formatting

[holla]

I am importing text which is a date, but not in the proper date format. When I change the format, it changes in the excel file, but when I save it as a .csv file, it saves the value that was imported. How can I get the formatting to actually change the value of the cell, rather than just changing its appearance in excel.

Thanks

 

[holla]

For example, it imports 3/24/2003, and when i format it looks in te cell like 03/24/03, but i yu lick on it, in the formula bar it still says 3/24/2003. And when i save it to .csv, what is in the formula bar is what shows up because it evidentily doesn't save formatting. I want to know how i can get the cell to actually be 03/24/03 even without the formatting.

 

[Mark W.]

The CSV file type does respect formatting. If you don't believe me. Change the extension from .csv to .txt and look at the contents using Notepad. Perhaps what's confusing you is that when you reopen the .csv Excel reapplies the mm/dd/yyyy format.

 

[holla]

I did see that the notepad file had the correct formatting, it is the fact that when I open it in excel it does not have the correct format. The files I make I have to send to a bank, and they want it in .csv, why I don't know if they are going to open it in excel anyway. What I would like is a macro, or steps I could record as a macro, to change the actual value of the cells in the column, without formattng, to the correct format so that when I open the .csv file in excel it has the proper format.

Thanks

 

[Mark W.]

I did see that the notepad file had the correct formatting, it is the fact that when I open it in excel it does not have the correct format. The files I make I have to send to a bank, and they want it in .csv, why I don't know if they are going to open it in excel anyway. What I would like is a macro, or steps I could record as a macro, to change the actual value of the cells in the column, without formattng, to the correct format so that when I open the .csv file in excel it has the proper format.

Thanks

Change your defined "Short date format" in the Regional Options Control Panel from M/d/yyyy to M/d/yy. Excel uses (defaults to) that formatting whenever it encounters a date that's either entered directly into a worksheet cell or contained in a .csv file.

 

[holla]

That did work on my computer, but I am sening the file to someone else to view. I am not sure how they view it, but they said the date was in the format of the actual cell value. When I enter the date 3/24/2003 it changes the value to 03/24/2003, but displays 03/24/03, I think that it will probably show the 03/24/2003 on the other persons computer. I think there should be something in excel that will change the text value to what it is formatted as, or if it isnt built in, then a macro could do it, but I am no very god at making macro. I think what I need is for it actually to change the value into what I would have it formatted as, rather than try to get my computer to display it correctly since I am sending it to someone else.