Transform date text into date serial number

    Hello experts,

    I have this application which will clean bank statements and customize the sheet in the required format. I have 2 kinds of files to convert in which the dates displayed are different in each format. (Test, Test2). The test2 sheet dates are giving the correct result but the test sheet is not converting the dates in the required format. Instead of 1st october it is displaying 10th january in the dd-mm-yyyy format. The issue is in the dates from 1st to 12th of every month. Can someone please help me to correct the code and get the correct result in both the cases.?

    After pressing the customize button check the date column of CleanData sheet. That is the only problem to be solved.

    Date Problem in Customize.xlsm

  • Go to Best Answer

    Hello,


    Since your initial dates are in fact Text ... and not what Excel requires i.e a serial number ...


    You do need to transform all your 'cells-Text' into a real Dates ...


    Based on cell A2 :

    Code
    =DATE(RIGHT(A2,2)+2000,VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2)))

    Hope this formula will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Carim

    Changed the title of the thread from “Need help to get the correct date format in the code” to “Transform date text into date serial number”.

    That I can do by selecting the A column from cell A2 to end in the raw data sheet, right click and convert it into dd-mm-yyyy. If possible, please help me to incorporate that within the code in getting the correct dates in the cleandata sheet without editing the raw data.

    Both the data are as it is as received from different clients. I have to just customize the data into the required columnar format with the code without any manual work.

    • Best Answer

    Hello again,


    The macro translation (from the macro recorder) of your latest comment would be :

    Code
    Sheet4.Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Like 1

    That worked with the data of test sheet but the data of test2 sheet is now showing the wrong dates. Is it not possible to get the dates in clean data sheet without altering the raw data dates.? Like, if the dates in raw data sheet are in dd/mm/yy then use the above added code else run the code without the lines you shared.

    As explained in message #2 ... when it comes to Dates ...


    In Excel they are actually stored as numbers ... and only then.... these numbers are formatted in order to be displayed as dates


    The dates are referred to as Serial numbers in Excel which visually look like Dates


    The date calendar in Excel starts on January 1st, 1900 ( or Day 1 ) ... for Excel this is THE beginning of Time !!!


    Hope this clarifies :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Like 1

    I have faced this date problem many a times in my past applications .Hopefully I will come up with something to solve this problem too. It just required the right code and your code is perfect for one of the files. I will try to add some additional conditions and try again. Thanks for you help Carim.

    Good to hear you are on the right track to fix all your Date questions ...


    Getting down to the root understanding will help you master all potential issues :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Quote from RAJESH SHAH

    I have faced this date problem many a times in my past applications .Hopefully I will come up with something to solve this problem too

    To make your XL experience easy, DO NOT apply manual alignment to ranges you will use in formulas. Why ? Because text is always left aligned by default in the cells. So, if you see a left-aligned date, you immediately know you have to (probably) work on it

    Congratulations on solving the problem :)


    But more importantly ...


    Congratulations on taking the time to master an essential Excel feature : the Date serial number :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Haha 1

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login