Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 08, 2020 07:26 PM
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()
)
Solved! Go to Solution.
Jun 08, 2020 08:17 PM
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?
Jun 08, 2020 08:17 PM
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?
Jun 08, 2020 09:25 PM
That definitely works and is super helpful. Thanks for the quick answer and clear explanation.
Why is the date formula different than Excel?
Jun 08, 2020 10:00 PM
Airtable does several things slightly differently from Excel. This is just one difference. Airtable’s DATETIME_DIFF
function is actually very similar to Excel’s DATEDIF
function.
If your question is answered, could you please mark whichever post contains your answer as the solution. Otherwise, could you please give a bit more details and a screen capture?