Forum Discussion
Solved
Problems sorting by time
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
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
To push SergeiBaklan'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,"-")));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
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
