Issue Sorting by Time

Michael Shubeck

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

Paul 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.

Richard Rymill SBP

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.

RichardR@smarterbusinessproceses.com

SC4SS- Cell Level Timestamp Manager-V3 RR.pdf

Paul 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.

Michael Shubeck

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!

Paul Newcome

@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.