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

[Tom_David]

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

[BillH]

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

[Tom_David]

Isn't it always the simple things... :-). Thx.

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

[BillH]

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. 

[Tom_David]

No rush. Thx.

[BillH · Accepted solution]

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

[Tom_David]

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%

[BillH · Accepted solution]

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/12...

I hope that can give you some ideas.