how format and arrange according to date. RRS feed

  • Question

  • Hi,

    When I sort data in a column with date as dd/mm/yy , it sort as per date . Hence with a common date and different years come together .  How can I sort with all three criteria , year next month and date ?

    please help.


    Thursday, October 29, 2015 8:19 AM

All replies

  • It appears that the dates are probably in text format. Try the following procedure.

    Use Number format to format the column as date. (This will not immediately change anything)

    Select any empty cell and enter the number 1 (one).

    Select and copy the cell with the number 1.

    Select all of the range with the dates and then right click and Paste Special -> Multiply. This should convert the text to dates without changing their value (any number multiplied by one remains the same number).

    Note: Dates are actually numeric values and formatting simply displays them as dates. Performing a math operation on the text dates forces them into numeric which are displayed as dates due to the number formatting.

    Regards, OssieMac

    • Edited by OssieMac Thursday, October 29, 2015 8:54 AM
    Thursday, October 29, 2015 8:51 AM
  • Hi ,

    Thank you , It gave different results . Some entry cannot be changed by this method or by formatting them to date or number or anything else. They do not show formatted as text either.


    Thursday, October 29, 2015 9:09 AM
  • Firstly I assumed that your regional date format is d/m/y and that the original data has date format as d/m/y. Is this correct?

    I suggest that all of the entries that appear to be left justified are in fact text. It could be that they have trailing spaces or other non printable characters. I am a bit confused by the entries that changed to numbers because I also suggest that if you previously formatted the column to dates as per my instructions then they should have converted to date format.

    Assuming that you have an original copy of the data then try the following method. Do not try to do this with data that you have already manipulated because you could get undesired results.

    1. Select the column of dates.
    2. Set the Number Format to General.
    3. While column still selected, Select Data ribbon.
    4. Select Text to columns icon (in data tools block towards centre of ribbon).
    5. Select option Fixed width and then click Next button.
    6. Ensure there are no vertical column lines through the data in the Data preview window. If there are then either double click them to remove or click on them and drag them out of the window.
    7. Click Next.
    8. Select Date option and then beside the date select the existing date format of the data. ie. Note that it is the existing date format that is required here; not necessarily the date format you want to end up with but they can be the same. The end result after finishing should be your own regional date format.
    9. Click Finish.
    10. You should finish up with the data in your regional date format.

    Regards, OssieMac

    Thursday, October 29, 2015 11:00 AM