Sort time 12 hours format AM/PM with expresion

[fortaleza40]

Hello Comunity,

I have a time field with 24 hours format, i formatted to 12 hours using this expression:

Mod(Hour(DateTimeField),12)&' '&time(DateTimeField, 'TT') as Hour

But now when i sort the field numerical or ascending is in disorder, how can i create a custom sort by expression? or sort this correcly?

[tresesco]

Try recreating hour field like:

Time(Frac(DateTimeField), 'h TT') as Hour

Then the sort order should work fine.

[fortaleza40]

The chart broke after use that formula 😞 and the sort option disable, do you know other way to do the same?

[lorenzoconforti · Accepted solution]

An option might be to use the dual function when you create the field; something like this:

dual( Mod(Hour(DateTimeField),12)&' '&time(DateTimeField, 'TT') , Hour(DateTimeField) )  as Hour