Re: Date formula question

Amy_Mcwilliam · ‎Dec 11, 2020

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 · ‎Dec 11, 2020

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 · ‎Dec 11, 2020

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 · ‎Dec 11, 2020

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

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

OR

Has the interest rate simply increased due to inflation?

Amy_Mcwilliam · ‎Dec 11, 2020

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

Thanks

Nathalie_Collin · ‎Dec 11, 2020

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.

Hope this helps.
Nathalie

Amy_Mcwilliam · ‎Dec 11, 2020

thank you, this is really helpful!