Issue Sorting by Time

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

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!