Need help sorting sheet based on earliest time

Hi all,

I've been working on an automation project where I webscrape data from travel sites and have them pasted into an Excel doc. Once all of the data has been entered into the Excel doc I would like to then sort the entire Sheet based on the Departure Time column (earliest departure to latest departure). Normally this wouldn't be very challenging, however when the data is scraped and pasted into the document it seems to be entering the times in different formats.

I am scraping data from 2 different sites, one is for flights and the other is for trains. The flights data is pasted into the document in the format "MM/dd/yyyy hh:mm:ss AM/PM". For the train data, I first need to convert it using Text to Column in order for Excel to recognize it as a time. Once that has been completed, Excel recognizes it in the format of "hh/mm/ss AM/PM".

I am able to then Format both columns into the format of "h:mm AM/PM". On the surface, everything looks correct. However, when I then try to sort the sheet based on departure time, it does not sort properly because the Excel doc still recognizes the data based on the original time formats rather than the new format.

I've attached an example of the Excel doc that I'm working with. If someone could take a look and help me figure out the steps necessary to solve the problem I would greatly appreciate it!

Please let me know if any additional information is needed.

Travel.xlsx