# Issue Sorting by Time

edited 02/13/20

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

• ✭✭✭✭✭✭

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.

• Paul,

This works perfectly for what I need. I really do appreciate you taking the time to create this formula and I'm certain this helps many others on the SS forum as well.

All the best and thank you!

• ✭✭✭✭✭✭

@Michael Shubeck

Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others looking for a similar solution can know that one may be found here.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!