Help

Using the FROMNOW() Function

Topic Labels: Formulas
1804 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Mark_Jeavons
4 - Data Explorer
4 - Data Explorer

Hi,

I am trying to use the FROMNOW() function to tell me when a transaction has expired within the last month.

Can someone explain why this formula does not return accurate results?

IF(FROMNOW({Expiry date}) <= 1, ‘month’, “Recently Expired”)

It works (i.e. it doesn’t return an error) but it is returning “Recently Expired” for every transaction that has expired, even ones that expired years ago!

Help please! :slightly_smiling_face:

Mark

2 Replies 2

Hi @Mark_Jeavons - the FROMNOW formula doesn’t work in the way you have tried above. By trying out FROMNOW on its own you can see the results it gives:

Screenshot 2020-01-11 at 22.22.48

Screenshot 2020-01-11 at 22.22.56

It is giving a string as a result, not an integer. Your formula reads:

If fromnow is less than 1, then display “month”, otherwise “recently expired”

As the string value returned by FROMNOW is never less than 1, the IF always returns the 2nd value.

Note that FROMNOW just takes a date argument, it doesn’t take a time unit argument (days, months) like other date based functions.

To get what you are after try using DATETIME_DIFF

DATETIME_DIFF(TODAY(), Date, 'days')

(I’ve used ‘days’, but you could also use ‘month’)

Then your formula field could be:

IF(DATETIME_DIFF < 28, 'Recently Expired')

Screenshot 2020-01-11 at 22.29.36

JB

Mark_Jeavons
4 - Data Explorer
4 - Data Explorer

Thanks Jonathan, that’s really helpful!