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:
Complex examples:
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:
