Skip to main content

Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.

DATETIME_DIFF({Sell}, {Buy Date} 'days')

Well, there may be a problem, but I can't see it... can anyone help with the formula to display the number of days between two dates 🤔 🤔? Thank you.

Here's what I'm getting for these 3 fields:

Buy Date, Sell, #Days

1/12/2020, (No End Date), NaN
7/10/2023, 1/31/2024, 17712000
1/1/2020, (No End Date), NaN
9/19/2023, 1/31/2024, 11577600

You're missing a comma - try DATETIME_DIFF({Sell}, {Buy Date}, 'days')

You're missing a comma - try DATETIME_DIFF({Sell}, {Buy Date}, 'days')


Isn't it always the simple things... 🙂. Thx.

Secondly, is there a way to NOT display NaN when the end date (Sell) is missing?

You would have to enclose the formula inside an if formula, logically if Sell is not blank display what you want, else display the result.  I'm running to a meeting so I can't go further right now, sorry. 

You would have to enclose the formula inside an if formula, logically if Sell is not blank display what you want, else display the result.  I'm running to a meeting so I can't go further right now, sorry. 


No rush. Thx.

This should result in a blank rather than NaN.  You can replace the blank with a string between the double quotes.
 
IF({End Date}, DATETIME_DIFF({Sell}, {Buy Date}, 'days'), "")
This should result in a blank rather than NaN.  You can replace the blank with a string between the double quotes.
 
IF({End Date}, DATETIME_DIFF({Sell}, {Buy Date}, 'days'), "")

That works. Thank you, Bill.

Here's a similar NaN issue.

Create a formula field which calculates profits using {fx buy} and {$ sell} fields AND displays the results as a % above 100% or a negative (-) % below 100%. Display results example: Buy @$10.00, Sell @$12.00= 20% OR Buy @$12.00, Sell @$10.00= -20%

 

NaN is just airtable identifying a result as "Not a Number".  It's so generic that you will never have a single solution, you just have to identify why you're seeing the result and in most cases and catch the individual instance before airtable does. 

https://support.airtable.com/docs/common-formula-errors-and-how-to-fix-them

If you google airtable remove NaN you'll see what I mean, each case is a little different.

I like the idea of using the iserror() function as shown in this thread.  https://community.airtable.com/t5/formulas/using-an-if-statement-to-change-a-nan-output-to-0/td-p/124723

I hope that can give you some ideas.  

Reply


Terms & Conditions