1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Format Issue

Discussion in 'Ask an Excel Question' started by Naresh Yadav, Jan 12, 2018.

  1. Naresh Yadav

    Naresh Yadav Active Member

    Messages:
    186
    Respected All,

    I have Data of Date which is stored in text and number format
    I am trying to get the result in "DD-MMM-YYYY" format through TEXT Formula
    But not getting my expected result

    Kindly find attached excel file and suggest a formula that can help me to come out from it

    activity_date Result Using Text Function Expected Result
    12-01-2017 12-Jan-2017 01-Dec-17
    12-02-2017 12-Feb-2017 02-Dec-17
    12-04-2017 12-Apr-2017 04-Dec-17
    12/13/17 12/13/17 13-Dec-17
    12/13/17 12/13/17 13-Dec-17
    12/14/17 12/14/17 14-Dec-17
    12/15/17 12/15/17 15-Dec-17

    regards
    Naresh

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    What you are asking for is not correct.

    If you see the first 3 items in cells A2 , A3 and A4 , their numeric values are :

    42747 , 42778 and 42837

    When the Date format is applied to these numeric values , the results are :

    12 January 2017 , 12 February 2017 and 12 April 2017

    If you want that these should be changed to 1 December 2017 , 2 December 2017 and 4 December 2017 , it means you want the data itself to be changed , not just its format.

    Is this correct ?

    Narayan
  3. Naresh Yadav

    Naresh Yadav Active Member

    Messages:
    186
    Respected Narayan Sir,

    Yes,Your observation is correct I want my data itself to be changed to the numeric value

    regards
    Naresh
  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,776
    Maybe,

    1] Copy A2:A8 to B2:B8

    2] Select B2:B8 >> "Text to Columns" >> in step 3 of 3, click "Date" and choose MDY >> Finish
    upload_2018-1-12_17-25-37.png

    Regards
    Bosco
    Last edited: Jan 12, 2018
  5. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    718
    Excel and date format can be a pain the ass (local formats). So in your original dates the months and the days are inversed. Where does the data come from? I mean, we can up with a lot of formulas, but ... 12/04/2017 may be correct (April 12th or December 4th) , whereas 12/13/2017 can only be the 13th of December.
    You need to know for sure what the original date format is.
  6. Naresh Yadav

    Naresh Yadav Active Member

    Messages:
    186
    Respected Bosco Sir,
    this is perfect, I wanted the same result which is offered by you,But couldn't understand that post doing "Text to columns" and and than using text function why I am getting my correct expected result as my data is having mix numerical and text value in the same format
    once again thank you so much sir for giving your valuable time

    regards
    Naresh
  7. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    718
    What I intended to aim for, when being sure there is a unique format...
    I tried a quicky with Power Query, but was rather disturbed I could not get it correct (while using local format in the change data type step). I did not expected that to happen. So fall back to "legacy" features did the trick, nice.

Share This Page