Using the FROMNOW() Function

[Mark_Jeavons]

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

[JonathanBowen]

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:

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

JB

[Mark_Jeavons]

Thanks Jonathan, that’s really helpful!