Help needed with an error I get with a formula

Technical Help
1

Hello,

So I have a formula that used to work perfectly that now gives me an error that I struggle to understand and that i find weird:

if(AND(AND(date_diff('dd’,now(),field(‘Echeance’))<31 , NOT(isblank(field(‘Echeance’)))),isblank(field(‘FAIT’))) ,TRUE,FALSE)

The formula calculates the date difference between now and a column called ‘Echeance’ and if the difference is smaller that 31 days and the columns ‘Echeance’ and ‘FAIT’ are not blank, it returns true.

Now, I get the following error : Argument 3 of date_diff is not a date.

The formula used to work flawlessly without issues and I didn’t change it.

‘Echeance’ is the result of the following formula: if(isblank(field(‘Durée’)),‘’,date_interval(field(‘Durée’))+field(‘Date’))

so it is either blank or a date. I don’t understand why it is suddenly not considered a date anymore or is the fact that some cells are blank considered a problem now.

Thank you for reading and for any help.

2

Hey @Hisoka, can you please try this one:

if(and(and(date_diff('day', now(), field('Echeance')) < 31, not(isblank(field('Echeance')))), isblank(field('FAIT'))), true, false)

What I did was change ‘dd’ to ‘day’ for the date_diff function. Hope it works now :slightly_smiling_face:

3

Hello Olga,

Thank you so much for the reply, unfortunately, it doesn’t work.
I think the problem might come from the blank cells in the ‘Echeance’ column:

if(isblank(field(‘Durée’)),‘’,date_interval(field(‘Durée’))+field(‘Date’))

I use an empty string so it doesn’t calculate a date when ‘Durée’ (duration) is empty but I think it might be creating a problem with the formula as it sees it as text.

I tried this version for the formula as well:

if(
AND(
NOT(isblank(field(‘Echeance’))),
if(isblank(field(‘Echeance’)), FALSE, date_diff(‘dd’, now(), field(‘Echeance’)) < 31),
isblank(field(‘FAIT’))
),
TRUE,
FALSE
)

But it gave me an error too saying the number of arguments exceeded 2.

Thank you again your the insight

4

Hey @Hisoka, maybe you can try this formula (so the issue is that ‘and’ can only have 2 arguments not 3):

if(and(not(isblank(field('Echeance'))), and(date_diff('day', now(), todate(field('Echeance'), 'YYYY-MM-DD')) < 31, isblank(field('FAIT')))), true, false)

If it doesn’t work, then maybe you could share with me privately the overview of all fields, and then I will try to rebuild the scenario and check the formula once again.

5

Yes, this one worked without an error. However, it does give TRUE even when the difference between now() and ‘Echeance’ is bigger than 31 so there is an issue for the logic of the what I intended to do.
Thank you still for helping.

1 Like
6

The formula preserves the logic when I use the French format for the date. So all is well, thank you so much again.