Sorting Time Variable in a Table

[former_member554641]

Need to clarify my previous post as it was deleted.

I created a variable named "Time" using the formula: =FormatDate([Date & Time];"hh:mm:ss A")

The variable returns the time of the Date/Time field which is expected. However, the time does not sort ascending. The values sort by the numeric values.

example:

01:07:26 PM

08:10:01 AM

The "AM" value needs to be listed before the "PM" value.

What is the recommendation to correct the sorting so that AM entries appear in ascending order prior to the PM entries?

Please advise if this is not clear or if additional details are needed.

Thank You

[amitrathi239 · Accepted solution]

Formatdate function convert the object data type from date to string.Sort always work on if data type is number or date.

instead of using format date function right click on the cell->format number->custom and type "hh:mm:ss A". this will change the format of date and keep the data type as date.

After that if you will apply sorting then it will work