IF formula question

[Amy_Mcwilliam]

Hello, I would like to create a formula that works out a date period for example the start date is 11/12/20 and 4 months later (calendar months) ie 11/04/2020 is there a formula I can use to automate this date of the 11/04 from my start date?

Thanks

[Nathalie_Collin]

Hi @Amy_Mcwilliam! Welcome to Airtable Community :wave:

The formula for this is;

DATETIME_FORMAT(DATEADD({Start Date},4,'month'),'DD-MM-YY')

What this formula does is that it takes your Start Date field and calculates 4 months into the future while keeping your preferred format of DD-MM-YY.

Hope this helps. Feel free to reach out if you run into any issues.
Nathalie

[Amy_Mcwilliam]

Nathalie_Collins:

DATETIME_FORMAT(DATEADD({Start Date},4,'month'),'DD-MM-YY')

Thank you, this worked! How would I could I also use a date formula on this example?
A loan is due to redeem on 11/12/20 - from the 12/12 the interest rate is higher. How do I pick the date range of 12/12 + 1 day to the 13/12?

[Nathalie_Collin]

The answer for this really depends on why the interest is increasing.

1. Is it a case, where after 30 days, any balance forward is deemed to be billable at a higher rate?

OR

2. Has the interest rate simply increased due to inflation?

[Amy_Mcwilliam]

your point 1. in this example after 4months it is now charged at a higher rate from the 13/12

Thanks

[Nathalie_Collin]

Perfect. So for this situation, let’s assume that you have the following fields;

• Start Date
• Due Date
• Interest PRIOR to Due Date
• Interest AFTER Due Date
• Amount Owing

Your formula would read;

IF(DATETIME_DIFF(TODAY(),{Start Date},'months')>= 4,(({Amount Owing}*{Interest Rate AFTER Due Date})+{Amount Owing}),(({Amount Owing}*{Interest Rate Prior to Due Date})+{Amount Owing}))

So to reiterate, this formula does the following;

• if the balance owing is $0.00 as of today, it displays $0.00
• if there is a balance owing, and;
  • the date is greater than 4 months, it calculates the new interest
  • the date is still less than 4 month, it calculates based on the original interest.

Here’s a base to demonstrate.

Airtable

Loan Interest Rate Intervals - Airtable

Explore the "Loan Interest Rate Intervals" base on Airtable.

Hope this helps.
Nathalie

[Amy_Mcwilliam]

Nathalie_Collins:

IME_FORMAT(DATEADD({Start Date},4,'month'),'DD-MM-YY')

thank you, this is really helpful!