FROMNOW() not in chronological order


#1

When a field using FROMNOW() is sorted from A-Z, the sort appears to be based on the first number of the output, rather than the time. Example:

Days should be considered smaller than months, which should be smaller than years. Output should be read the same as DATETIME_DIFF():

Secondary issue: FROMNOW shows deadlines a day earlier than the actual deadline (see difference in images above)


#2

I think if you have A-Z option to sort, that is a string and not a time/date/number. So what you get seems normal to me.


#3

A-Z is the only option for the output of FROMNOW(). Yet FROMNOW() is categorized as a Date/Time Function, and the description in the Formula field reference says “Calculates the number of days between the current date and another date.”

Doesn’t it seem more normal that I would want to put things in order of when they’re next due?


#4

You could find part of an anwser to this here :

I encountered a similar problem with TODAY() vs. NOW()

It won’t resolve a thing, but it was very helpful to try to understand why… :sweat_smile:

Concerning the bug, FROMNOW() simply seems to be a « friendly user format » field in opposition to DATETIME_DIFF().
:woman_shrugging:


#5

I just comment on what I see. Maybe the documentation should specify that the return value is a string, with a correspondant ' days' suffix.

You can make it a number just with INT(SUBSTITUTE(Days, ' days', '')), and you could order by number, on top of using DATETIME_DIFF() :sweat_smile:


#6

@Elias_Gomez_Sainz has it right: If the result of a formula field is, in part, a string, the entire thing is a string — and the string '5 years' is less than the string '6 days'. That the only available sort options are 'A-Z'/'Z-A' is a dead giveaway the result is a string.

And Date/Time Functions return dates, numbers, or strings, depending on the function.

Frankly, I’d suspect it is a rare to-do list that commingles items due in days and years.

One possibility would be to create a hidden field with DATETIME_DIFF(), sort FROMNOW(), and group by bins of days: < 30 (or is it < 7; does FROMNOW() ever return in weeks?), < 365, and > 365).