Date formatting Solved/Closed

Question

Hello,

I am currently downloading a report from an external source (csv file format) into excel and one of the fields (column P) should be represented with a date however I simply get ###### hence i have to copy the data of that column into a seperate worksheet and insert a fromula =dategregtojul(cell) to covert that number into date format. 

Was wondering if there is a quicker way around this??/

Thanks guys, Jay

rizvisa1 · Answer

What is the text in that column. If you could mention what the initial value is in that column, one may be able to suggest some thing

jay · Answer

Hey,

the when i open the csv file the format is as such; 20080820

rizvisa1 · Answer

not knowing how you initially open it, But once the data is in excel you can use text to column

1. Select Column A (presuming this is the offending column)
2. Click on "Data" and choose "Text to Column"
3. Choose "fixed Width"
4. make sure that the line that shows up on next screen completely covers the date (most probably position 10)
5. Select "Column Data Format" to be 'Date' with appropriate date format (it seems your is YMD)
6. Click Finish

jay · Answer

Hi,

Thanks that works also in addition to the dategreg formula.

But i was wondering if there is some for of maybe VBA i could insert so that column get auto formatted when i paste the data into my excel sheet?? maybe a bit of a long shot?? 

cheers mate

Date formatting

4 responses

Similar discussions

Subscribe To Our Newsletter!