How to change date format in excel please help.

#1
Dear friends i got i eod data.But the data is not updating in my meatstock.Later i found what was wrong with that data and why it was not updating in meatastock.Actually the date has not been separated.For eg. for for 01/01/2004 it was 01012004.Now i come to my question "how to change the date format 01012004 to 01/01/2004 to the data.It is impossible to change it manually from 2000 to 2008 so is there any other way of doing in excel.I have attached a sample file of the data.
 

AW10

Well-Known Member
#2
assuming that you have 01012004 value in cell A1. Type this formula in new column.
=LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4)

If there is any problem, then try
=CONCATENATE(LEFT(A1,2),"/",MID(A1,3,2),"/",RIGHT(A1,4))

just ensure that A1 is showing date in 8 numbers.. i.e. leading zero is not removed.

hope this helps..
 
#3
I could not make it..I dont have much knowledge of excel.Further all the data comes into a single cell A that is the problem.can u pls illustrate step by step.
 
Last edited:

AW10

Well-Known Member
#4
Raja, With my limited knowledge of excel, I can't think of this transformation in excel in one or two steps.. But if you need to upload it into Metastock, then I am not sure if metastock can read an excel file.

I can think of following way out
1) ask the vendor to supply the data in metastock compatible format. Currently he has given you CSV file (comma seperated value) which is not accepted by Metastock.

2) post this query in Metastock or datafeed section so that other experts in these areas can suggest the right approach

3) check on internet or write to Equis or the company from where u got Metastock and ask them about how to load csv file into MS.

All the best.
 
#5
Thank u for ur kind help sir.But metastock accepts csv files i daily update my data either csv or text file.The problem is the data vendor has not separated the date month year by "/" symbol.I compared this data file and other data file which i have already updated daily to metastock and found the only differece between the files is the date format which i need to correct.Thank once again for helping attitude.
 

oxusmorouz

Well-Known Member
#6
I could not make it..I dont have much knowledge of excel.Further all the data comes into a single cell A that is the problem.can u pls illustrate step by step.
AW10's transformation works.

Try doing this:
1) Convert the whole date column (A in this case) into another column (say F)
2) Copy the whole column F into a new text file and save that text file
3) Delete column A and F in the excel sheet
4) Copy paste the contents of the notepad into column A
 

pakatil

Well-Known Member
#7
Dear Rajath,

While opening Text file in to Excel, it will ask for conversion parameters. In the step 3, set Column Data Format to Date and complete import. There u have the converted data.

Cheers
 

RSI

Well-Known Member
#9
Dear Rajath,

While opening Text file in to Excel, it will ask for conversion parameters. In the step 3, set Column Data Format to Date and complete import. There u have the converted data.

Cheers
Good suggestion. I never knew this. Thanks
 

Similar threads