Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

How to turn DATETIME_DIFF result into a number

Topic Labels: Formulas
Solved
Jump to Solution
415 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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