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

Solved
Jump to Solution
962 1
cancel
Showing results for
Search instead for
Did you mean:
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
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'), "")
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.

7 Replies 7
9 - Sun

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

6 - Interface Innovator

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

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

9 - Sun

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.

6 - Interface Innovator

No rush. Thx.

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

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%

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.