As many are aware, the "sort by time" function is not (yet) available in SmartSheet as it is in Excel. Essentially SmartSheet treats time sequentially as opposed to recognizing AM/PM. So sorting a time field would, for instance, show 8:10 AM followed by 8:15 PM and so on. There has been some suggestions posted in terms of using military time or having separate AM/PM columns, but for me this isn't the best solution. Additionally, there have been several postings that suggest a work around of creating a formula in a new column, sorting by that column, and then hiding that column. In that light, I tried using one of the suggested formulas as shown below:
=((VALUE(LEFT(Time1, FIND(":", Time1) - 1)) + (COUNTIF(Time1, FIND("pm", @cell) <> 0) * 12)) * 60) + VALUE(MID(Time1, FIND(":", Time1) + 1, 2))
This formula works well except for instances containing 12PM. Essentially, when sorting, this falls after 11 PM when it should be before 1PM. On a different post I found the following that accounts for this:
IF(VALUE(LEFT(Time7, FIND(":", Time7) - 1)) = 12, 0, (VALUE(LEFT(Time7, FIND(":", Time7) - 1))))
The post states that if the hour is 12, pretend it is zero so that both 12 am and 12 pm will come before 1 am and 1 respectively.
My thought is to combine these functions somehow, but I have not been successful. Any help with this (or other suggestions) is greatly appreciated.
Credit for these formulas goes to Adam Overton.
Thank you
