How to turn DATETIME_DIFF result into a number

Question

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

Justin_Barrett · Accepted Answer

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

Cathy_Anderson · Answer

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!!!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded