[DONE] Add a number of months to a date

1

I need to add a number of months (in Paid Terms field) to a date (in License From field).
This is what I was able to do figure out so far:

Date(If(Month([License From]) + [Paid Terms] > 12,Year([License From]) + 1,Year([License From])),If(Month([License From]) + [Paid Terms] > 12,Month([License From]) + [Paid Terms] - 12,Month([License From])),Day([License From]))

Sadly, it doesn’t work. The calculated field shows exactly the same date as in License From. Nothing is being added. :-/

I’ve already spent half an hour of my life on it. Why is this so hard? Jesus.
In Excel, I can do this in a few seconds. :-/

2

OK, I fixed a small error but the formula still doesn’t work.
The field is not being updated with new calculated data. sigh

Date(
If(
Month([License From]) + [Paid Terms] > 12,
Year([License From]) + 1,
Year([License From])
),
If(
Month([License From]) + [Paid Terms] > 12,
Month([License From]) + [Paid Terms] - 12,
Month([License From]) + [Paid Terms]
),
Day([License From])
)

3

It is likely that at least one entity has a day of the month that does not exist in the month which is ‘Paid terms’ in the future.
For example, adding 1 month to January 31st will take you to February 31st, which obviously doesn’t exist.
If this happens, the formula calculation will likely fail everywhere.

4

Thanks.
Then the question becomes, how do I add X months to an existing date?
I’ve been working on this for an hour already. :disappointed:

5

Something like this should work

Date(
  Year(CurrentDate) +
    RoundDown((Month(CurrentDate) + IntervalPeriod - 1) / 12, 0),
  Month(CurrentDate) +
    IntervalPeriod -
    12 * RoundDown((Month(CurrentDate) + IntervalPeriod - 1) / 12, 0),
  Least(
    Day(
      Date(
        Year(CurrentDate) +
          RoundDown((Month(CurrentDate) + IntervalPeriod - 1) / 12, 0),
        Month(CurrentDate) +
          IntervalPeriod +
          1 -
          12 * RoundDown((Month(CurrentDate) + IntervalPeriod) / 12, 0),
        1
      ) - Days(1)
    ),
    Day(CurrentDate)
  )
)

You’d use Paid Terms instead of IntervalPeriod

6

Holy crap! What a formula. I would not have been able to do that if I gave myself a whole weekend.
And it works. Thank you!! :pray:t2:

7

It’s actually not too dissimilar to what you were attempting, but it is more generic.
For example, your code for calculating the year

will be incorrect if the Paid Terms number is greater than 12 (which may not happen in your specific case, I realise).

By using this

Year(CurrentDate) + RoundDown((Month(CurrentDate) + IntervalPeriod - 1) / 12, 0)

it’s possible to have the year correct for any integer value.

Similarly, this code

Month(CurrentDate) + IntervalPeriod - 12 * RoundDown((Month(CurrentDate) + IntervalPeriod - 1) / 12, 0)

ensures that the month number is never greater than 12.

Finally, the last bit of code prevents the day of the month from exceeding the highest possible value for the relevant month, by comparing the day number with the last day of the month. The last day of the month is determined by calculating the first day of the subsequent month and subtracting 1 day:

Least(
Day(Date(<calculated year>,<calculated month + 1>, 1) - Days(1)),
Day(CurrentDate)
)
8

Month(x) formula was released today