FROMNOW() not in chronological order

Topic Labels: Formulas
2355 5
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

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)

5 Replies 5

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?

8 - Airtable Astronomer
8 - Airtable Astronomer

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

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

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:

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