My date and time entries in my excel file are written in the form of: Feb 18 at 3:50pm. All of this is in one cell. I was wondering if there is a way to sort this out (arrange chronologically)
22 Answers
Please right click one cell > Format cells, check whether you set Custom format like mmm dd \at h:mm AM/PM for cells.
If yes, you may set other formats for cells to check this issue.
You can extract the date/time value from the text in the format shown using this formula (assuming the value is in A1):
=DATEVALUE(MID(A1,FIND(" ",A1)+1,2)&"/"&MONTH(DATEVALUE(1&LEFT(A1,3)))) + TIMEVALUE(MID(A1,FIND(" ",A1,FIND(":",A1)-3)+1,LEN(A1)-FIND(" ",A1,FIND(":",A1)-3)-2) & " " & RIGHT(A1,2))
then format the cell containing the formula as suggested by Emily in her answer using custom formats to return it to the original look: mmm dd \at h:mm AM/PM
then sort the column and it should sort from earliest to latest.
NB: this assumes the dates are this year.
In the picture below, column B contains the formula above, columns C and D simpy have =B1 but are formatted differently.
Regards Justin
