Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE
SOLVED

# Problems sorting by time

Copper Contributor

# 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

3 Replies
best response confirmed by Mike Smith (Copper Contributor)
Solution

# Re: Problems sorting by time

with formula

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

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

# Re: Problems sorting by time

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,"-")));``````

# Re: Problems sorting by time

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

1 best response

Accepted Solutions
best response confirmed by Mike Smith (Copper Contributor)
Solution

# Re: Problems sorting by time

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