Date formula question

Ask the community
#1

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

#2

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

#3

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?

#4

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

  1. Has the interest rate simply increased due to inflation?
#5

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

Thanks

#6

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

#7

thank you, this is really helpful!

1 Like
#8

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.