Formulas: Need help regarding classification of data

  • Hi all pals..

    I have a question and I have uploaded the example as attached.

    I need to classify the dates according to months. The piece of data was extracted from SAP, hence, when i opened from Excel, its a Text Delimted file and i actually went about formatting it and also specify that it is a date property.

    What I have done before but not shown in this example spreadsheet...
    I actually formatted the cells for the date field... though it may appear Jul-03 for me, but its no way i could paste the data onto another cell, it would not be the date field, it would appear numbers for me, i used the Paste-Speical-Method. So for my case, i dun think it is very meaningful and useful.

    Now, is there another way?

    I need to based on the date, and classify it according to months with year. For instance, if the the date is 11.07.03, the result return would be 0703.

    The reason i need to get 0703 format is because, later on, i would need to used 0703 as one of my argument to perform some calculation.

    hear form you soon.

    james :tumble:

    Warmest regards

  • highlight the column | go to data | text to columns... | hit next twice | select DMY from the date data format options | hit finish.

  • James:

    I'm not sure that I fully understood what it was that you were looking for. See the attached example and see if this answers.


  • James,

    Not sure that I am 100% with you either, but if you use the replace tool to replace all the periods (fulstops) in col A with / then format the column with custom format mmyy you will get the results you are after.

    Hope that helps

Participate now!

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