+ Reply to Thread
Results 1 to 7 of 7

Sorting just the text dates by macro

  1. #1
    Registered User
    Join Date
    05-23-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    37

    Red face Sorting just the text dates by macro

    Hi Excel Experts,

    I am stuck in an sorting issue. I wanted to sort just the text date cells, which is located in a Date Column. so that i can correct them, by using Text to column procedure. These i come across as i get report in .csv format, which i import to excel.

    If i do Text to column, by selecting whole column, it also crisscrosses/messes the dates which are showing proper date & month. There are 15000+ rows, plus many sheets. Need help in selecting just TEXT DATE Cells by macro/code, so that i can apply below TEXT to column just for this Text Date.

    Attached an Excel file as a Sample, Please see EUR TAB (G COLUMN), which is of original file.

    Thanks a lot in advance.

    TEXT Dates to Sort by Macro.jpg
    ____
    If the dates are text from a CSV file and look like 4/15/2015 and NOT 4-15-2015 you can use Text to columns.
    1. Select the dates
    2. Click on the Data Tab, Text to columns
    3. Click Next, Next, select Date and DMY
    4. Click Finish

    The dates will be converted to the regional settings for dates.

    Regards
    Ram

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sorting just the text dates by macro

    1 option would be to use =ISNUMBER() in a helper column next to the dates, then include that in the filter and filter only on FALSE

    You will be left with just the non-date dates which you can copy/paste to fix.

    Although, if you take that entire column and use T2C/Delemited/Next/Next/Date/DMY it converted them all OK for me
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sorting just the text dates by macro

    Hi Ram,

    Here is a macro that does all the conversion of column G to column H.

    Please Login or Register  to view this content.
    It was harder to do it by text to columns. See if this works for you.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    05-23-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Sorting just the text dates by macro

    Hi FDibbins,

    Thansk for the quick reply!

    When i do the text to column, the dates of proper date format, other than TEXT do change, if i sellect whole column for Text to column - DMY. Ex: 10/02/2015 (10 Feb 2015)
    Step 1 - before text to column.jpgStep 2.jpg

    Appreciate if you can assist me to recognise, just the TEXT files - so that only this can be sorted out, to change.

    Regards
    Ram
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Sorting just the text dates by macro

    This replaces text dates in column G with Excel dates.

    Please Login or Register  to view this content.
    Ben Van Johnson

  6. #6
    Registered User
    Join Date
    05-23-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Sorting just the text dates by macro

    Hi ProtonLeah & MarvinP,

    I thank both of you, for valuable assistance!

    Both the codes provided worked well, with the latter one correcting in the same column, thats excellent, as i was not able to put extra column in Org sheet.

    However, the dates are just not proper. Its showing month as dates in one cell, in next cell its accurate, please check the 3 QC Check cell Ref - F3, F159, F14747 - in attached excel sheet.

    With pure guess work, applied Text to Column to dates which i got after macro, it all got corrected.

    Can you assist, if there is a Code to put for Column G, to do text to column -DMY after Macro.

    Regards
    Ram

  7. #7
    Registered User
    Join Date
    05-23-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Sorting just the text dates by macro

    Thanks.. Have recorded the macro for Last task - Text to column.

+ 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. Macro for sorting rows based on dates in column G
    By Scotcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-03-2014, 10:12 AM
  2. [SOLVED] Pivot table is sorting values with text first then dates?!?!?
    By leesrubberroom in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-09-2013, 07:19 AM
  3. [SOLVED] Sorting dates with added text issue.
    By skyping in forum Excel General
    Replies: 6
    Last Post: 11-26-2012, 09:38 PM
  4. Having issues sorting a column with dates and text
    By Honeybee129 in forum Excel General
    Replies: 3
    Last Post: 05-03-2007, 06:36 PM
  5. [SOLVED] Sorting dates:How do I sort a column filled with dates by Month, Day, then Year?
    By Heather in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-22-2005, 10:05 PM

Tags for this Thread

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