FROMNOW() not in chronological order

[Patrick_Flanaga]

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)

[Elias_Gomez_Sai]

Patrick_Flanagan1:

field using FROMNOW() is sorted from A-Z

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.

[Patrick_Flanaga]

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?

[Ptt_Pch]

Patrick_Flanagan1:

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

You could find part of an anwser to this here :

DATETIME_DIFF() missing a day : Why ? and How to? [not quite Solved] Dates and timezones

IIRC, TODAY() equates to midnight today (that is, the moment today began), while NOW() equates to the time when the function was called — and both are given in GMT.¹ Accordingly, if swapping TODAY() for NOW() (or vice-versa) ‘fixes’ your problem, it’s probably because the Δ between midnight GMT and the current time in GMT just happens to have been enough to skew the result correctly. Unfortunately, this means at other times of the day the problem is likely to reappear. . __________ This can l…

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… :grinning_face_with_sweat:

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

[Elias_Gomez_Sai]

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() :grinning_face_with_sweat:

[W_Vann_Hall]

@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.

Patrick_Flanagan1:

FROMNOW() is categorized as a Date/Time Function,

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

Patrick_Flanagan1:

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

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).