Solution for adding months to a date (native Knack features)

I recently had the need to add months (1, 2 or 6) to a date.

As per previous topics there is no obvious way to do this in Knack, due to the complexities of different length months and leap years, and the absence of a function to add months to a date.

Some examples of what we are trying to achieve are below:

15 Jan 2022 plus 1 month should be 15 Feb 2022
31 Jan 2022 plus 1 month should be 28 Feb 2022
31 Jan 2024 (leap year) plus 1 month should be 29 Feb 2022

There’s a surprising way to do this in Knack that I discovered by chance today. Explained below.

On average, 1 month is 0.0833333 years (1 month divided by 12 months = 0.08333…). This is an inaccurate average that does not account for different length months and leap years, but it doesn’t seem to matter in this workaround.

To set this up we need 3 fields:

  • Initial date (date field)
  • Years to add (number field)
  • Final date (equation field)

The 3 fields are set up as per the 3 images below:

Now, we set our “Years to add” field to 0.083333333 (1 month).

And we find that it calculates the final date successfully.

Straightforward example:
image

Complex examples:
image

image

This can of course be extended with more complex logic, eg methods to autofill the “Years to add” field with different values based on a user selection etc. This is just the basic concept.

It also works with other values above 1 month in duration.

eg adding 3 months: we change our “Years to add” field value to 0.25 and it works as expected still:

image

image

8 Likes

Hi

I definitely need this for my loan management system app. I can then be able to show both start and end dates in the Loan Agreement. Example: A client get approved for a loan with repayment term of 24 months on 28 June 2022 ( By this means 28 June 2022 + 24 = 28 June 2024), I am right?

This is fantastic! Thank you so much for taking the time to share this solution!

Correct

Callum,
thank you so much for this solution! Was exactly what i needed at this moment.
I needed to add N months and then fast-forward to the last day of that month, so I added a Formula field that took your result and enclosed in getDateLastDayOfMonth({…}).
it’s golden!!

Thanks @Callum.Boase - just had the need to do exactly this. A great workaround :+1:
Saved me a whole load of work.