Forum Discussion
Need Help Sorting This Date/Time Column
I have 8000 text messages that have been imported into Excel. Column A has the date/time and Column B has the text itself. The format of A is:
Monday, November 19, 2018 8:43:07 AM
Monday, November 5, 2018 4:26:42 PM
Monday, October 22, 2018 2:27:01 PM
The problem is, Excel is sorting the numerical order, meaning the "1" in November 19 is being sorted before the "5" and all of the dates are being sorted in groups of the day of the week, so all of Monday text messages are sorted by MONDAY even though the Monday text messages are throughout the entire year. Can someone help me sort all of this in the correct order?
5 Replies
Without helper cells, a succinct alternative would be this formula in H3 of mathetes's file:
=--REPLACE(A3,1,FIND(",",A3)+1,"")
With H3 formatted like G3, the result of the foregoing formula looks like this:
Thank you for the work you put into that. However, with the time striped out, the text messages might be sorted by the date but the time of when they were sent will no longer be in order since they are no longer being sorted by both date and time. Twifoo
The time is still there. Cell H3 didn't show the time simply because the custom format didn't include it. If cell H3 is custom-formatted as "mmmm d, yyyy h:mm:ss AM/PM", then it would also show the time like this:
GetAKitty4465 I'm attaching a spreadsheet that contains three formulas. I didn't test it against your whole sample list, so it's possible it might encounter a glitch or two with dates of signficantly different lengths, but here's how this works.
- Column A contains the text values like you have them.
- Column B finds the first comma in that text, and adds 2, which is ALWAYS going to be the first letter of the month
- Column C finds the second comma in the text, and adds 6, which will ALWAYS be the end of the year
- Column D, then, uses those two numbers to find the text that begins with number in col B, ends with ColC minus ColB (this last subtraction calculates the length of the date string)
- Column E converts that to Excel's date value
- Column F simply is a more elaborate formula which does the work of columns D and E in one step
The second row just shows, in cell F3, that you can format the cell to read as a "user friendly date", but the value remains a sortable date value.
Once you've copied these formulas all the way down your 8000 rows, you'll be able to sort based on actual date value.
