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
iMac, iOS 10.2.1
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:
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
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:
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
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.
If you really just want the month name in the cell, select the column, then format the column as text using the cell formatter:
"…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.
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:
Dates are sorted chronologically; text values are sorted alphabetically (and follow the date values).
Regards,
Barry
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?
Probably a better picture showing headers as well
Many thanks. Should have thought of that one - I normally work the system to make things work. All sorted
Numbers will not sort months correctly
