Traderji.com - Online community for Indian investors & traders!


How to change date format in excel please help.

Discuss How to change date format in excel please help. at the Data Feeds within the Traderji.com - Online community for Indian investors & traders!; Dear friends i got i eod data.But the data is not updating in my meatstock.Later ...


Go Back   Traderji.com - Online community for Indian investors & traders! > TOOLS & RESOURCES > Data Feeds

Data Feeds Real time and end-of-day data feeds.


Reply
 
Thread Tools
  #1  
Old 9th January 2009, 10:17 PM
rajatheroyal's Avatar
Member
 
Join Date: Apr 2008
Location: Tiruchendur,Tamilnadu.
Posts: 411
Thanks: 177
Thanked 179 Times in 91 Posts
rajatheroyal has a spectacular aura aboutrajatheroyal has a spectacular aura about
Default How to change date format in excel please help.

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.



Reply With Quote
Sponsored Links
  #2  
Old 9th January 2009, 11:14 PM
Member
 
Join Date: Jan 2007
Posts: 2,918
Thanks: 2,941
Thanked 8,035 Times in 2,042 Posts
AW10 has a reputation beyond reputeAW10 has a reputation beyond repute
AW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond repute
Default Re: How to change date format in excel please help.

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..
Reply With Quote
The Following 2 Users Say Thank You to AW10 For This Useful Post:
oxusmorouz (10th January 2009), rajatheroyal (10th January 2009)
  #3  
Old 9th January 2009, 11:29 PM
rajatheroyal's Avatar
Member
 
Join Date: Apr 2008
Location: Tiruchendur,Tamilnadu.
Posts: 411
Thanks: 177
Thanked 179 Times in 91 Posts
rajatheroyal has a spectacular aura aboutrajatheroyal has a spectacular aura about
Default Re: How to change date format in excel please help.

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 by rajatheroyal; 10th January 2009 at 01:26 AM.
Reply With Quote
  #4  
Old 10th January 2009, 03:15 AM
Member
 
Join Date: Jan 2007
Posts: 2,918
Thanks: 2,941
Thanked 8,035 Times in 2,042 Posts
AW10 has a reputation beyond reputeAW10 has a reputation beyond repute
AW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond reputeAW10 has a reputation beyond repute
Default Re: How to change date format in excel please help.

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.
Reply With Quote
The Following 2 Users Say Thank You to AW10 For This Useful Post:
oxusmorouz (10th January 2009), rajatheroyal (10th January 2009)
  #5  
Old 10th January 2009, 07:54 AM
rajatheroyal's Avatar
Member
 
Join Date: Apr 2008
Location: Tiruchendur,Tamilnadu.
Posts: 411
Thanks: 177
Thanked 179 Times in 91 Posts
rajatheroyal has a spectacular aura aboutrajatheroyal has a spectacular aura about
Default Re: How to change date format in excel please help.

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.
Reply With Quote
  #6  
Old 10th January 2009, 07:54 AM
Member
 
Join Date: Sep 2006
Location: Chennai
Posts: 1,004
Thanks: 35
Thanked 120 Times in 72 Posts
oxusmorouz has a spectacular aura aboutoxusmorouz has a spectacular aura aboutoxusmorouz has a spectacular aura about
Default Re: How to change date format in excel please help.

Quote:
Originally Posted by rajatheroyal View Post
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
Reply With Quote
The Following 3 Users Say Thank You to oxusmorouz For This Useful Post:
AW10 (10th January 2009), rajatheroyal (10th January 2009), VJAY (10th January 2009)
  #7  
Old 10th January 2009, 11:01 AM
Member
 
Join Date: Oct 2006
Location: Bangalore
Posts: 1,330
Thanks: 3,727
Thanked 1,657 Times in 716 Posts
pakatil has a brilliant futurepakatil has a brilliant futurepakatil has a brilliant futurepakatil has a brilliant futurepakatil has a brilliant futurepakatil has a brilliant futurepakatil has a brilliant futurepakatil has a brilliant futurepakatil has a brilliant futurepakatil has a brilliant futurepakatil has a brilliant future
Default Re: How to change date format in excel please help.

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
Reply With Quote
The Following User Says Thank You to pakatil For This Useful Post:
rajatheroyal (10th January 2009)
  #8  
Old 10th January 2009, 05:18 PM
rajatheroyal's Avatar
Member
 
Join Date: Apr 2008
Location: Tiruchendur,Tamilnadu.
Posts: 411
Thanks: 177
Thanked 179 Times in 91 Posts
rajatheroyal has a spectacular aura aboutrajatheroyal has a spectacular aura about
Default Re: How to change date format in excel please help.

I have solved my proplem.Thanks to leo_3255 who helped me on teamviewer and aw10,oxummorouz and pakatil for your ideas.
Reply With Quote
  #9  
Old 10th January 2009, 05:51 PM
RSI RSI is offline
Member
 
Join Date: Nov 2006
Posts: 545
Thanks: 31
Thanked 256 Times in 99 Posts
RSI is a jewel in the roughRSI is a jewel in the roughRSI is a jewel in the rough
Default Re: How to change date format in excel please help.

Quote:
Originally Posted by pakatil View Post
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
Reply With Quote
  #10  
Old 26th January 2009, 08:30 AM
Member
 
Join Date: Sep 2006
Location: Chennai
Posts: 1,004
Thanks: 35
Thanked 120 Times in 72 Posts
oxusmorouz has a spectacular aura aboutoxusmorouz has a spectacular aura aboutoxusmorouz has a spectacular aura about
Default Re: How to change date format in excel please help.

If there are more than 66,000 cells in the csv/txt file, excel may not read it. Try using this instead


Reply With Quote
The Following User Says Thank You to oxusmorouz For This Useful Post:
rajatheroyal (4th February 2009)
Reply

Bookmarks


Advertise Here


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off



All times are GMT +5.5. The time now is 08:50 AM.

Indemnity, Disclaimer & Disclosure Notice:
By visiting Traderji.com you automatically indicate that you agree to our Forum Rules, Indemnity, Disclaimer & Disclosure Notice and General Content Disclaimer Notice and indemnify Traderji.com, its associates and related parties of all claims howsoever resulting from the usage of the forum/site.
Disclaimer: Trading or investing in stocks & commodities is a high risk activity. Any action you choose to take in the markets is totally your own responsibility. You are recommended to make appropriate enquiries and seek appropriate advise before sending money, incurring any expenses, acting on recommendations or entering into any commitment in relation to any advertisement published here. Traderji.com does not vouch for any claims made by the advertisers of products and services. Traderji.com will not be held liable for any consequences in the event such claims are not honoured by the advertisers. Traderji.com will not be liable for any, direct or indirect, consequential or incidental damages or loss arising out of the use of any information by anybody mentioned anywhere on this site.
Disclosure: The information in this forum is neither an offer to sell nor solicitation to buy any of the securities mentioned herein. The writers may or may not be trading in the securities mentioned.
All names or products mentioned are trademarks or registered trademarks of their respective owners.
General Content Disclaimer Notice:
In light of our policy of encouraging candid, open exchanges of views and the rapid distribution of information originating from many sources, Traderji.com cannot determine the accuracy or legality of any information that may be uploaded to the forum. Opinions, advice and all other information expressed by participants in discussions are those of the author. You rely on such information at your own risk. You are urged to seek professional advice for specific, individual situations and not rely solely on advice or opinions given in the discussions. Since Traderji.com is an open and free discussion forum, any comments made by members of this forum in their posts reflect their own views and not of the owner or administrator of Traderji.com. Thus the owner/administrator indemnify themselves of all claims whatsoever and will not be liable or responsible for any members comments/views in this forum Traderji.com. Although we constantly delete all irrelevant content and/or SPAM, if you should find any objectionable or offensive posts made by members of this forum which you would like to bring to our notice for removal then please Contact Us.
 


Copyright © www.Traderji.com 2001 - , All rights reserved.

Recommended Websites - www.TradersEdgeIndia.com - www.TradingPicks.com - www.MasterOfTrading.com

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70