Why isn't my Excel formula working?

Question

Can I create a formula for calculating Interest like the one I use in Excel:
UPB*{Interest Rate}*(({To Date}-{Bill of Sale Date}))/365
In Airtable, this formula generates a NaN error message:
I got it to work by breaking it out into two cells and using the formula recommended for differences in dates from Airtable’s support page on NaN errors:
Cell labeled “test:”
IF(
AND(
{Bill of Sale Date},
{To Date}
),
DATETIME_DIFF({To Date},{Bill of Sale Date},‘days’),
BLANK()
)

And in another cell, this formula:
UPB*{Interest Rate Total}*(test/365)

kuovonne · Accepted Answer

The “NaN” error message means that the result of the calculation is not a number. In this case, it is not a number because you cannot directly subtract dates. You correctly figured out to use the DATETIME_DIFF function to calculate the number of days between the dates. You can now combine different parts of the formula together:

The IF part of the formula just checks to make sure that there are values in all of the input fields. (If any of the input fields can have a valid value of 0, this formula will need a slight adjustment.)

Notice that the main part of the formula is the same as your original formula, except for calculating the difference between the dates.

IF(
  AND(
    {UPB},
    {Interest Rate},
    {To Date},
    {Bill of Sale Date}
  ),
  UPB * {Interest Rate} * DATETIME_DIFF({To Date},{Bill of Sale Date},'days') / 365
)

You may also need to set the formatting options for the formula field to show your desired number of decimal places. The default is to round the number to the nearest integer.

If this answers your question, please mark this post as the solution. Otherwise, could you please give a bit more details and a screen capture?

Jim_Levy · Answer

That definitely works and is super helpful. Thanks for the quick answer and clear explanation.Why is the date formula different than Excel?

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded