Skip to main content

Months Active

is a formula that returns what appears to be a number.

IF({First Gift Date}, DATETIME_DIFF({Last Gift Date},{First Gift Date},‘month’),"")

Number of Gifts

is a count field

I’m trying to create a field that returns an frequency

{Months Active}/{Number of Gifts}

But it is throwing an #Error

What am I over looking?

I’m pretty sure it has something to do with the DATETIME_DIFF returning a date and not a number

DATETIME_DIFF() is returning a number. What’s throwing the system off is your trailing empty string in that formula. Because that’s there, Airtable is turning that number into a string in order to keep all possible output options consistent.

Thankfully you can nix that empty string. That third piece of the IF() function is optional. Leaving it out lets Airtable figure out what data type should be output to the empty field based on the data that’s returned from the other portion. Your formula then becomes this:

IF({First Gift Date}, DATETIME_DIFF({Last Gift Date},{First Gift Date},'month'))

DATETIME_DIFF() is returning a number. What’s throwing the system off is your trailing empty string in that formula. Because that’s there, Airtable is turning that number into a string in order to keep all possible output options consistent.

Thankfully you can nix that empty string. That third piece of the IF() function is optional. Leaving it out lets Airtable figure out what data type should be output to the empty field based on the data that’s returned from the other portion. Your formula then becomes this:

IF({First Gift Date}, DATETIME_DIFF({Last Gift Date},{First Gift Date},'month'))

Thank you! That little tip fixed a whole lot of NaN if my files. Appreciate you once again!!!

Terms & ConditionsAccessibility statement