Time Sort with seconds AM PM in Sharepoint viwe

I created a single line text column to record the time in HH:MM:SS AMPM

But now I need it to sort properly.

It's sorting in numerical order but not in actual chronological order.

so for example (ascending sort order)
4:17:00 pm
9:45:01 am
11:34:56 am

where the 4pm item should be last.

Can I remedy this with a calculated column?
Who is Participating?
trax75Connect With a Mentor Commented:

Datetime saves the date correct with the time (like the created or modified field).

You save already the time in a text column - so the easiest way is to create a calculated field with "=DATEVALUE([My Text])+TIMEVALUE([My Text])" as formula and set the result type to number. You can use this column to order your entries.


ps: [My Text] stands for the column name, storing your time values as text.

you have to create a Date-Column to get this list ordered the right way. Or Change the current column to date.

sullisnyc44Author Commented:
but the date time column is in 5 minute intervals. ???

That's not useful to me. I need HH:MM:SS AMPM. Can I get that from a date time column?

Also I really only need the time (but if I have to use the date AND time then fine).
sullisnyc44Author Commented:
I think I owe you a beer! YOU Sir, are awesome.

Here's my code:
=IF(ISERROR([Line Report Date]+TIMEVALUE([Time DOWN])),"",([Line Report Date]+TIMEVALUE([Time DOWN])))
sullisnyc44Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.