Since the date and time come out as text and may not easily convert if said text doesn't match your system settings, folks using Excel may find these two formulas useful.
The first converts the text into an Excel date value without using your system's date/time settings. Sorts using the column will work properly and you can use the normal Excel date formats to display the result however you'd like.
The second formula converts the result of the first to text the way I like it, using "Thursday" instead of "08/17/2017", "Midnight" and "Noon" instead of 12:00 AM and 12:00 PM (respectively), and dropping minutes when an event starts at the top of the hour (minute == 00). Nicer for display, but not suitable for sorting.
- Insert two columns to the right of the Start Date & Time column. (It should be column N, making O and P your new blank columns.)
- Select cell O2 and paste this into the formula bar: =DATE(MID(N2, 7, 4), MID(N2, 1, 2), MID(N2, 4, 2)) + TIME(IF(MID(N2, 12, 2)<>"12", VALUE(MID(N2, 12, 2)), 0) + IF(RIGHT(N2, 2)="PM", 12, 0), MID(N2, 15, 2), 0)
- Select cell P2 and paste this into the formula bar: =TEXT(O2, "dddd ") & IF(MINUTE(O2)<>0, TEXT(O2, "h:mm AM/PM"), IF(HOUR(O2)=0, "Midnight", IF(HOUR(O2)=12, "Noon", TEXT(O2, "h AM/PM"))))
- Select both cells and extend or copy/paste them down through all rows in the sheet.
- Name your new columns and hide the ones you don't want to see.
Hope this makes sense. :)