Skip to main content
Solved

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

  • February 2, 2024
  • 7 replies
  • 39 views

Tom_David
Forum|alt.badge.img+10

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

Best answer by BillH

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

7 replies

BillH
Forum|alt.badge.img+24
  • Brainy
  • February 2, 2024

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


Tom_David
Forum|alt.badge.img+10
  • Author
  • Known Participant
  • February 2, 2024

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?


BillH
Forum|alt.badge.img+24
  • Brainy
  • February 2, 2024

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
Forum|alt.badge.img+10
  • Author
  • Known Participant
  • February 2, 2024

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
Forum|alt.badge.img+24
  • Brainy
  • Answer
  • February 5, 2024
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
Forum|alt.badge.img+10
  • Author
  • Known Participant
  • February 5, 2024
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
Forum|alt.badge.img+24
  • Brainy
  • February 5, 2024

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.