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)
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.
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.
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?
+4You 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… :grinning_face_with_sweat:
Concerning the bug, FROMNOW() simply seems to be a « friendly user format » field in opposition to DATETIME_DIFF().
:woman_shrugging:
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?
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:
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?
@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).
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
