Hi I've searched this for the correct way to do this but have had no luck. I think I am making it far more difficult than it should be.

In a Google doc, we have multiple columns of data with client info being pulled from an external source into 1 large sheet of raw data. One column is a list of times with AM/PM included (1:00 PM, 10:15 AM, etc). However, that column is imported as a text string despite being formatted as time from the original source.

We then use that 1 sheet of data to make multiple pivot tables. I want to make a pivot table that sorts by time. The sort function on the pivot table will only do it alphanumerically, so the list is not sorted accurately if I just click the sort button at the top of the pivot table. I cannot edit the source, so I need a work around after I already have the list of times on the raw data sheet.

So if my raw sheet of data has a list of times as a string like this:

Column I
Times
1:00 PM
3:00 PM
9:00 AM
10:15 AM
10:50 AM
8:30 AM
9:20 AM
3:00 PM


How can I convert that string to a time, that will sort properly in a pivot table in google docs?