Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Why isn't my Excel formula working?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

1
1040
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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()

)

- And in another cell, this formula:

UPB*{Interest Rate Total}*(test/365)

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

Reply

3 Replies 3

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?