How to change date format in excel please help.

Discussion in 'Data Feeds' started by rajatheroyal, Jan 9, 2009.

  1. rajatheroyal

    rajatheroyal Active Member

    Joined:
    Apr 26, 2008
    Messages:
    433
    Likes Received:
    202
    Trophy Points:
    43
    Location:
    Tiruchendur,Tamilnadu.
    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.
     
  2. AW10

    AW10 Well-Known Member

    Joined:
    Jan 8, 2007
    Messages:
    2,918
    Likes Received:
    8,037
    Trophy Points:
    113
    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..
     
    rajatheroyal and oxusmorouz like this.
  3. rajatheroyal

    rajatheroyal Active Member

    Joined:
    Apr 26, 2008
    Messages:
    433
    Likes Received:
    202
    Trophy Points:
    43
    Location:
    Tiruchendur,Tamilnadu.
    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: Jan 10, 2009
  4. AW10

    AW10 Well-Known Member

    Joined:
    Jan 8, 2007
    Messages:
    2,918
    Likes Received:
    8,037
    Trophy Points:
    113
    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.
     
    rajatheroyal and oxusmorouz like this.
  5. rajatheroyal

    rajatheroyal Active Member

    Joined:
    Apr 26, 2008
    Messages:
    433
    Likes Received:
    202
    Trophy Points:
    43
    Location:
    Tiruchendur,Tamilnadu.
    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.
     
  6. oxusmorouz

    oxusmorouz Well-Known Member

    Joined:
    Sep 21, 2006
    Messages:
    1,001
    Likes Received:
    133
    Trophy Points:
    63
    Location:
    Chennai
    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
     
    AW10, VJAY and rajatheroyal like this.

  7. pakatil

    pakatil Well-Known Member

    Joined:
    Oct 13, 2006
    Messages:
    1,627
    Likes Received:
    2,067
    Trophy Points:
    113
    Location:
    Bangalore
    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
     
    rajatheroyal likes this.
  8. rajatheroyal

    rajatheroyal Active Member

    Joined:
    Apr 26, 2008
    Messages:
    433
    Likes Received:
    202
    Trophy Points:
    43
    Location:
    Tiruchendur,Tamilnadu.
    I have solved my proplem.Thanks to leo_3255 who helped me on teamviewer and aw10,oxummorouz and pakatil for your ideas.:)
     
  9. RSI

    RSI Well-Known Member

    Joined:
    Nov 27, 2006
    Messages:
    545
    Likes Received:
    260
    Trophy Points:
    63
    Good suggestion. I never knew this. Thanks
     
  10. oxusmorouz

    oxusmorouz Well-Known Member

    Joined:
    Sep 21, 2006
    Messages:
    1,001
    Likes Received:
    133
    Trophy Points:
    63
    Location:
    Chennai
    If there are more than 66,000 cells in the csv/txt file, excel may not read it. Try using this instead
     
    rajatheroyal likes this.

Share This Page