+ Reply to Thread
Results 1 to 26 of 26

How to convert exported text date to desired date format with excel vba.

  1. #1
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    How to convert exported text date to desired date format with excel vba.

    Hello ExcelForum Members,

    I am exporting some data form a text file (.txt) to excel sheet. One of the column contains the date like 20130704 (yyyymmdd). When I click on any of cells containing the date values, it shows the general datatype. What I am trying to do is to convert these dates in general data type format to date data type so that I can use these dates in calculations. I want it to be done with the help of vba because the size of exported data varies. So I want to make sure that once the vba code is executed, it converts all the values in that column until the last row.

    Is there any way to do this?
    Any help would be appreciated.
    Thanks in advance.

    sktneer
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by sktneer View Post
    I am exporting some data form a text file (.txt) to excel sheet
    How ? manually or with vba ?
    If solved remember to mark Thread as solved

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by patel45 View Post
    How ? manually or with vba ?
    I am exporting the data through vba using pastefromclipboard procedure and then converting text to data in column A. The data in clipboard is with comma delimiter.

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to convert exported text date to desired date format with excel vba.

    Maybe:

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by sktneer View Post
    I am exporting the data through vba using pastefromclipboard procedure and then converting text to data in column A. The data in clipboard is with comma delimiter.
    why did you attach a xlsx file without your code ?

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by JOHN H. DAVIS View Post
    Maybe:

    Please Login or Register  to view this content.
    Thanks John for your reply. But the code is working only once. Next time when I export the data on the same sheet. The date column contain only ########. And getting error on the following line...
    cell.Value = Left(cell, 4) & "/" & Mid(cell, 5, 2) & "/" & Right(cell, 2)

    Any solution to this?

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to convert exported text date to desired date format with excel vba.

    If you are adding data to the same sheet then maybe:

    Please Login or Register  to view this content.

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by JOHN H. DAVIS View Post
    If you are adding data to the same sheet then maybe:

    Please Login or Register  to view this content.
    Thanks. But it is still giving error on the following line...

    cell.Value = Left(cell, 4) & "/" & Mid(cell, 5, 2) & "/" & Right(cell, 2)

    any clue?

  9. #9
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to convert exported text date to desired date format with excel vba.

    What does the error say? What is the error number?

  10. #10
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by JOHN H. DAVIS View Post
    What does the error say? What is the error number?
    The code executes without any error but the date column shows ###### sign only when I delete the existing data and export the fresh data on the same sheet and remains same even after running the code.

  11. #11
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: How to convert exported text date to desired date format with excel vba.

    It worked on the sample you provided? Can you perhaps provide a sample which errors? Otherwise I can't see what the problem is and doubt if I can offer more help.

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How to convert exported text date to desired date format with excel vba.

    I do not if you want to overwrite column A or want the output in a separate sheet. The output is in column B, but can easily changed to column A. You need to be on active sheet when you run the code.
    Attached Files Attached Files

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Why don't you import the data using either File>Open... or Data>From text file...?

    Then you should be able to convert to a 'real' date automatically.

    Or select the column with dates, goto Data>Text to columns... and select YMD for column data format on the third step.

    If you want code you can use Record Macro... when doing any of the above.
    If posting code please use code tags, see here.

  14. #14
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by AB33 View Post
    I do not if you want to overwrite column A or want the output in a separate sheet. The output is in column B, but can easily changed to column A. You need to be on active sheet when you run the code.
    Thanks a lot AB33. Your suggested code worked like a magic. But the problem is I am not able to convert it according to my requirement.
    My sheet has two date columns B and C and these columns date values exists from cell B2 and cell C2 because row 1 has headers in it.
    I want to convert dates to the desired format in these columns itself, don't want the output in other columns.
    I tried to convert your code and succeeded to convert it for column B but not for column C.
    It would be a great help if you suggest how can I change the code as per my requirement.

    Thanks indeed for finding some time to help me.
    Waiting for your reply.

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to convert exported text date to desired date format with excel vba.

    This will do the conversion without any helper columns.
    Please Login or Register  to view this content.

  16. #16
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by Norie View Post
    This will do the conversion without any helper columns.
    Please Login or Register  to view this content.
    Thanks Norie for helping me. Would you please explain a bit what will this piece of code do? Because I am not expert of VBA. But I tried to include your code within my code (which is a sub-routine known as pastefromclipboard()) and my code inserts the data at desired location, but when it comes to your code then, it gives an error '1004' (No data was selected to parse.)

    Would you please guide me that how to use this code.

    Please find the sheet attached with my code for pasting the data from clipboard. This code runs well very first time, but when I run it second time the date columns show only ########, which is strange to me.

    You can copy the below data and run the macro pastefromclipboard() to see what it does.


    SBO507175300,20130705,20130920,62
    SBO864070985,20130705,20130919,62
    SBO177492601,20130705,20130927,77
    SBO710237444,20130705,20130920,62
    SBO962342877,20130705,20130930,59
    SBO19704115,20130705,20130918,81
    SBO191859391,20130705,20130927,67
    SBO477581883,20130705,20130929,72
    SBO337574486,20130708,20130929,82
    SBO58479395,20130708,20130924,78
    SBO462419969,20130712,20130912,114
    SBO555497224,20130712,20130929,69
    SBO628084891,20130712,20130924,74
    SBO569899162,20130719,20130923,82
    SBO350825729,20130719,20130926,62
    SBO171753411,20130719,20130927,49
    SBO613615026,20130719,20130930,58
    SBO925727239,20130719,20130925,53
    SBO366112445,20130719,20130922,67
    SBO123327454,20130719,20130924,54
    SBO251091491,20130719,20130927,84
    SBO458342174,20130719,20130927,68
    Regards
    Attached Files Attached Files

  17. #17
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to convert exported text date to desired date format with excel vba.

    I'm a little confused, the code you have in the sub format sub in Module2 appears to do what you want.

    It splits the data with comma as the delimiter and converts columns B and C to 'real' dates.

    So what's the actual problem?

  18. #18
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by Norie View Post
    I'm a little confused, the code you have in the sub format sub in Module2 appears to do what you want.

    It splits the data with comma as the delimiter and converts columns B and C to 'real' dates.

    So what's the actual problem?
    Probably you didn't read my post #16 carefully. It runs perfect first time but gives error by showing date columns like #######. You can try it.

    Thanks & Regards.

  19. #19
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Why would you run it a 2nd time?

    By the way, where is the data coming from and how/why does it end up in the clipboard?

  20. #20
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by Norie View Post
    Why would you run it a 2nd time?

    By the way, where is the data coming from and how/why does it end up in the clipboard?
    I copy the data from the web and instead of pasting this data into a text file , save it and then import data from the saved text file to the excel sheet, I just want to copy the data from the web and then run the code in excel sheet which would extract the data present in the clipboard.

    I have to repeat this process everyday i.e. I copy the new data from the web and come back to the excel sheet and here my code has a line of code which clears the older data already there in the sheet before extracting the new data from the clipboard . That's it. Its all about automation of the work which I need to do everyday and macros are best friends to help you in this scenario.

    Thanks & Regards.
    Last edited by sktneer; 10-02-2013 at 10:38 AM.

  21. #21
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Why are you using the clipboard?

    Excel has built-in methods to import data from the web.

    Those methods can easily be automated with code.

  22. #22
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by Norie View Post
    Why are you using the clipboard?

    Excel has built-in methods to import data from the web.

    Those methods can easily be automated with code.
    Because when I export the data with the link provided on a webpage, a new browser window opens and that contains the data with comma delimiter in the format as same as I provide in my post #16. From there I have to copy the data and use it as per my requirement. I have tried to export directly from the web but that page doesn't contain the data link to do that. It only gives you the option to open it in a new window with csv format.

    Regards.

  23. #23
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to convert exported text date to desired date format with excel vba.

    So you wouldn't be interested in code that could navigate to the webpage, click the data link to open the new window with the data and then grab the data in the new window?

  24. #24
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by Norie View Post
    So you wouldn't be interested in code that could navigate to the webpage, click the data link to open the new window with the data and then grab the data in the new window?
    No. Not at all.

  25. #25
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to convert exported text date to desired date format with excel vba.

    I thought you wanted to automate the procedure?

  26. #26
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to convert exported text date to desired date format with excel vba.

    Quote Originally Posted by Norie View Post
    I thought you wanted to automate the procedure?
    I am paying you regards as you are senior member of the forum but I think you are pulling my legs. Please ignore my thread if it irritates you. I am not an expert of VBA, that's why I asked my query here in this forum, but it you think my query is silly, I am really sorry for that I had no intention to hurt anybody here.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Cannot convert date (which is in text format) to date in numeric format
    By geniuspro in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-19-2012, 09:21 AM
  2. Formula to convert text to a date in a date format
    By mikeburg in forum Excel General
    Replies: 5
    Last Post: 08-24-2011, 09:15 AM
  3. convert date format to text in all excel files in a folder
    By farrukh in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-31-2011, 01:12 PM
  4. Help: How do I convert a text date into a real date format
    By japorms in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2006, 01:36 PM
  5. [SOLVED] Date format not correct when you convert a CSV text file in Excel
    By Scarab in forum Excel General
    Replies: 2
    Last Post: 11-16-2005, 08:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1