Forum Discussion

Mike Smith's avatar
Mike Smith
Copper Contributor
Aug 20, 2022
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...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 20, 2022

    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

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 20, 2022

Mike Smith 

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

Resources