Problems sorting by time

[Mike Smith]

I have a spreadsheet with a Time column which is a time range. The input form assumes time is AM unless it has a "p", as shown below. I've tried online solutions, but everything I've tried results in 1 pm before 10 (am). What must I do to get it to sort correctly? Thanks.

3:10p - 4:00p

10:30-11:30

7:00- 10:30

8:00 p - 9:30 p

8:30-9:00p

1:15p-3:30p

What it should be:

7:00- 10:30

8:30-9:00p

10:30-11:30

1:15p-3:30p

3:10p - 4:00p

8:00 p - 9:30 p

[#2 · Accepted solution]

@Mike Smith 

You may add helper column

with formula

=TIMEVALUE( SUBSTITUTE( TRIM( LEFT(B3, FIND("-",B3)-1 ) ), "p", " pm") )

after that select both, Data->Sort, sort by column Two

[#3]

@Mike Smith 

To push @Sergei Baklan's solution on a step using Excel 365

WorksheetFormula
= LET(
  split, DROP(REDUCE("",timeInterval, Splitλ),1),
  times, TIMEVALUE(SUBSTITUTE(split,"p"," pm")),
  SORT(times));


Splitλ
= LAMBDA(acc,interval, 
  VSTACK(acc, TEXTSPLIT(interval,"-")));

[#4]

Thanks so very much for that formula. Found I needed to create a column for it, then populated the formula in the cells. It was so good to see a correct sort!

Mike