sorting rows

duw

I have a lsit of names with an application date and time. I've tried sorting the columns by date and then time. The time column will not sort properly. It has some AM times at the end of the list and PM times all over. How can I fix this?

kowal

Maybe if possible you can use a formula that changes 12:00 to 24:00 in that case you will not have AM & PM but other standard and the sorting shall work.

https://community.smartsheet.com/discussion/65268/change-time-into-military#:~:text=@Cheryl%20Petrich%20Assuming%20it%20would%20always%20be,24%2C%20otherwise%20convert%20the%20time%20as%20usual.&text=Give%20that%20formula%20a%20try.

if you however want to keep the AM and PM the 24h time can be only helper column for sorting but you still keep original time for visibility.

hope it helps

Paul Newcome

This formula should give you a working sorting column:

=VALUE(YEAR([Date Column]@row) + "" + RIGHT("0" + MONTH([Date Column]@row), 2) + RIGHT("0" + DAY([Date Column]@row), 2) + SUBSTITUTE(TIME(RIGHT([Date Column]@row, LEN([Date Column]@row) - FIND(" ", [Date Column]@row)), 1), ":", ""))