When I try and sort using the column with date and time in it, it does all of the numbers in order as opposed to the date in order.
I have made sure the data is formatted as date and time but it still does this.
It was copied from another page and pasted in then I formatted it again to be sure.
Nothing I try works.
Please help
It is because your "dates" are text. They are sorting alphabetically as text does. If you try to change them to date&time format, it won't recognize them as date&time because they are not in a date format Numbers knows. They will revert right back to being text.
With a few temporary columns they can be converted to date & time with formulas. What I don't know is the years that go with all your dates. I will assume Jan 1 through today are 2024 and all other dates are 2023. I will also assume the column with your "dates" is column D,
E2 =TEXTBETWEEN(D2," "," -",1)
F2 =TEXTAFTER(D2,"- ")
G2 =IF(DATEVALUE(E2)>TODAY(),2023,2024)
H2 =DATEVALUE(E2&" "&G2)+TIMEVALUE(F2)
The custom format in column H is given below. Before creating it first look at all the dates in column H and make sure they became dates. Dates will align to the right in the cell. Text will align to the left. Any cells with error triangles you'll have to type the date in by hand; it means the text "date" didn't fit the pattern I used for the formulas.
Sorry there is no format for am/pm, just AM/PM.
From here, select all the dates in column H. Copy, and Paste Formula Results back to the same cells. This will get rid of the formulas, replacing them with the results. You can delete the other temporary columns and your original column that had the "dates" and use this column instead.
Enter new dates like usual. For example like 3/1/24 11:17am or 1 mar 2024 11:17am. If it accepts it as a date it will format it like the others. If it does not it will be text. I recommend using 3/1/24 format or all lower case when entering the dates. If it converts to the custom format with uppercase then it was accepted as a date. If it stays just as you typed it, it is text.
It is because your "dates" are text. They are sorting alphabetically as text does. If you try to change them to date&time format, it won't recognize them as date&time because they are not in a date format Numbers knows. They will revert right back to being text.
With a few temporary columns they can be converted to date & time with formulas. What I don't know is the years that go with all your dates. I will assume Jan 1 through today are 2024 and all other dates are 2023. I will also assume the column with your "dates" is column D,
E2 =TEXTBETWEEN(D2," "," -",1)
F2 =TEXTAFTER(D2,"- ")
G2 =IF(DATEVALUE(E2)>TODAY(),2023,2024)
H2 =DATEVALUE(E2&" "&G2)+TIMEVALUE(F2)
The custom format in column H is given below. Before creating it first look at all the dates in column H and make sure they became dates. Dates will align to the right in the cell. Text will align to the left. Any cells with error triangles you'll have to type the date in by hand; it means the text "date" didn't fit the pattern I used for the formulas.
Sorry there is no format for am/pm, just AM/PM.
From here, select all the dates in column H. Copy, and Paste Formula Results back to the same cells. This will get rid of the formulas, replacing them with the results. You can delete the other temporary columns and your original column that had the "dates" and use this column instead.
Enter new dates like usual. For example like 3/1/24 11:17am or 1 mar 2024 11:17am. If it accepts it as a date it will format it like the others. If it does not it will be text. I recommend using 3/1/24 format or all lower case when entering the dates. If it converts to the custom format with uppercase then it was accepted as a date. If it stays just as you typed it, it is text.
Another Upvote!
Thank you soooo much - lifesaving info!! I will note this for future reference.
Very well explained
Cheers
Sorting dates in Numbers - I have looked at other threads
