Date Difference Returns NaN With Blank Date Field

[Jim_Graziano]

I’m struggling with the syntax to have the Aging field go blank or 0 instead of NaN when there is no invoice date. IE, calculate/display the # of days since invoice was issued, return blank or 0 if invoice has not yet been issued.

This is the formula I am trying to use. The Invoice Date field is InvoiceDate and I’m using the TODAY function:

IF(
AND(
{Start},
{End}
),
DATETIME_DIFF({End},{Start},‘days’),
BLANK()
)

Thanks!

[Jeremy_Oglesby]

The first thing I’d suggest doing is removing the call to BLANK() at the end of your formula. If you want an IF() function to return nothing if your conditions are not met, you can just leave that final argument blank – it is optional. It should be very rare that you actually need to specify BLANK() as an output for a formula.

Start there and see if it helps at all.

I’m also a bit confused, though, because you mention a field called {InvoiceDate} and that you are using the TODAY() function, but I don’t see either of those in your formula.

[Jim_Graziano]

Sorry for the confusion. That is the sample formula from the Help section. I replaced the Start and End with what I thought they should be. The field InvoiceDate is pretty obvious how it should go into the formula, I’m not sure of the syntax when using the TODAY function. That’s probably where my problem is. This is the formula I’m using now, which works, but returns NaN if there is no invoice date.
DATETIME_DIFF(TODAY(), {InvoiceDate}, ‘days’)

[Jeremy_Oglesby]

Ah, ok – so you are actually missing the IF() condition entirely then. You need to check for the presence of a value in the {InvoiceDate} field before trying to run the value in that field through the DATETIME_DIFF() function, like this:

IF(
   {InvoiceDate},
   DATETIME_DIFF(
      TODAY(),
      {InvoiceDate},
      'days'
   )
)

[Jim_Graziano]

That worked perfectly, thank you!
I’m new to Airtable, so I’m still getting used to everything. This is the first formula I’ve tried.
I’ve wanted to find a database like this for a long time. Powerful, yet fairly easy to configure and web-based with a good mobile app. I was doing my sales lead database in Excel before, which is a good spreadsheet but a lousy database! I could have done it in Access, but I haven’t used it in years and the learning curve would be horrendous. They have done a great job with this, it’s (almost) everything I’ve been looking for!
Thanks again!

[Jeremy_Oglesby]

Jim_Graziano:

I could have done it in Access, but I haven’t used it in years and the learning curve would be horrendous.

Not to mention, it’s not officially supported anymore, so it’s a dead-end product, and there’s no good web interactivity to it at all.

Jim_Graziano:

I was doing my sales lead database in Excel before, which is a good spreadsheet but a lousy database!

Excel can make a pretty respectable database for small scale stuff like that, mostly if it’s used locally. Using an input form view in conjunction with VBScript macros that translate that data into a table tab does a decent job. But again, it’s the weakness of its web interactivity that cripples it.

Airtable is quite a good solution for the sort of thing you are wanting to do.

[Jim_Graziano]

Didn’t realize that about Access. Yes, Excel will work, but changes can be so time consuming.
Airtable is exactly what I was looking for.
Thanks!

[kuovonne]

Jeremy_Oglesby:

it’s not officially supported anymore, so it’s a dead-end product, and there’s no good web interactivity to it at all.

Do you mean the online version of Access isn’t supported anymore or do you mean all versions of Access? Access 2019 seems to be supported until 2025, per this support page.