Oct 29 2019 09:07 AM
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?
Oct 29 2019 10:13 AM - edited Oct 29 2019 10:14 AM
@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.
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.
Oct 29 2019 11:45 AM
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:
Oct 29 2019 12:20 PM
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
Oct 29 2019 12:33 PM
@GetAKitty4465 Sorry, from your original post it looked like you were just looking for the date to be the basis for the sorting.
Here's the same spreadsheet as before, but with an additional column that just contains the Time Value in a sortable form. Add it to the spreadsheet and sort first by Date, then by Time (all in one sort; just sequence the fields in that order)
Oct 29 2019 12:38 PM
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: