Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

Question: Numbers will not sort months correctly

Whether I sort using the individual column or the sort function I get two lots of months. By that I mean it goes from Jan to December then Jan to December again in the same column.


I am using Numbers on mac


User uploaded file

iMac, iOS 10.2.1

Posted on

Reply
Question marked as Solved
Answer:
Answer:

Change the format back to "Automatic" (or use undo to get back to the column formatted as "Automatic or Date")

you can add another column named "Month Number" in the example below:


User uploaded file


B2=MONTH(A2)


this is shorthand for... select cell B2, then type (or copy and paste from here) the formula:

=MONTH(A2)


to fill down, select cell B2, copy

select cells B2 thru the end of column B, or as needed, paste


now sort on "Month Number" rather than the name

Posted on

Question marked as Helpful

Mar 15, 2018 6:40 AM in response to martyna320 In response to martyna320

This is because the month names are actually only the month portion of a date/time value. Since you did not type in a complete date/time value, Numbers made up the rest of the information. You can check this by clicking any one of the cells and looking at the bottom left corner to see the date that is actually in the cell.

User uploaded file

If you really just want the month name in the cell, select the column, then format the column as text using the cell formatter:

User uploaded file

There’s more to the conversation

Read all replies

Page content loaded

Question marked as Helpful

Mar 15, 2018 6:40 AM in response to martyna320 In response to martyna320

This is because the month names are actually only the month portion of a date/time value. Since you did not type in a complete date/time value, Numbers made up the rest of the information. You can check this by clicking any one of the cells and looking at the bottom left corner to see the date that is actually in the cell.

User uploaded file

If you really just want the month name in the cell, select the column, then format the column as text using the cell formatter:

User uploaded file

Mar 15, 2018 6:40 AM

Reply Helpful (1)

Mar 15, 2018 6:44 AM in response to Wayne Contello In response to Wayne Contello

Thanks.

Although not completely solved the issue as it now sorts alphabetically, it is much less of an issue and one I can work around. Many thanks.

I haven't tried as it would take way to long but if I just entered the month and the date (not the year) would this then sort correctly if I choose one of the cell formatting options?

Mar 15, 2018 6:44 AM

Reply Helpful
Question marked as Solved

Mar 15, 2018 8:37 AM in response to martyna320 In response to martyna320

Change the format back to "Automatic" (or use undo to get back to the column formatted as "Automatic or Date")

you can add another column named "Month Number" in the example below:


User uploaded file


B2=MONTH(A2)


this is shorthand for... select cell B2, then type (or copy and paste from here) the formula:

=MONTH(A2)


to fill down, select cell B2, copy

select cells B2 thru the end of column B, or as needed, paste


now sort on "Month Number" rather than the name

Mar 15, 2018 8:37 AM

Reply Helpful (1)

Mar 15, 2018 12:55 PM in response to martyna320 In response to martyna320

"…If I just entered the month and the date (not the year) would this then sort correctly if I choose one of the cell formatting options?"


If the cell is formatted to Automatic or to Date and Time, and text alignment is set to Automatic, the characters entered in column A of this example will be interpreted as either text or as Date and Time values, and aligned according to that interpretation, left (text) or right (date & time) automatically.

Column B contains a formula (in B2, =A2) that copies the content of the cell in the same row of column A. Format of column B is set to Date and time, displaying the Date part as yyyy/mm/dd and the time part as hh:mm:ss.

User uploaded file

If only the month name (full or short form) is entered, it will be interpreted as text, as seen in rows 2, 3, 4 and 10.

If the month name and a day number are entered, Numbers will interpret it as a date, convert the first letter of the month name to a capital (if necessary), and complete the date and time value with the current year and a time part set to 00:00:00 (midnight at the beginning of that day), then display the parts of the result actually entered.


Sorted ascending on column A, this is the result:

User uploaded file

Dates are sorted chronologically; text values are sorted alphabetically (and follow the date values).


Regards,

Barry

Mar 15, 2018 12:55 PM

Reply Helpful
User profile for user: martyna320

Question: Numbers will not sort months correctly