Issue Sorting by Time

Welcome to the New Smartsheet Online Community

You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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


  • Richard Rymill SBPRichard Rymill SBP Top Contributor

    Michael I believe we have the answer ready built for you Using the API and what we call "Cell level Timestamp manager" which can record date & time down to minutes from which you can then create formulas to use this data. We would need to test it against your use case but i think we are on the right track?

    I've attached an explainer sheet as it is part of a list of API solutions we have written.

    [email protected]

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    =VALUE(IF(VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) <> 12, VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + IF(CONTAINS("p", [Start Time]@row), 12), IF(CONTAINS("p", [Start Time]@row), 12, 0)) + MID([Start Time]@row, FIND(":", [Start Time]@row) + 1, 2))

    The above will convert your time into a numerical value based on the 24 hour format. 12pm stays as 12 and 12am converts to 0.

Sign In or Register to comment.