Help

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

Solved
Jump to Solution
756 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Tom_David
6 - Interface Innovator
6 - Interface Innovator

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

2 Solutions

Accepted Solutions
BillH
9 - Sun
9 - Sun
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'), "")

See Solution in Thread

BillH
9 - Sun
9 - Sun

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.  

See Solution in Thread

7 Replies 7

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. 

No rush. Thx.

BillH
9 - Sun
9 - Sun
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%

 

BillH
9 - Sun
9 - Sun

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.