Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

How to turn DATETIME_DIFF result into a number

Topic Labels: Formulas
Solved
Jump to Solution
1415 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Cathy_Anderson
6 - Interface Innovator
6 - Interface Innovator

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

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

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