Formula to add a number count of months to a date column

Gabriela Colvis

Hello,

I am trying to create a formula that adds a cell that has a number, and a cell that is a date. The number is the count of months to add to the date. I have the date column formatted as "Date" and the number columns as text/number. The destination column is formatted as a date.

This gets me close to it but not exact.

=[Repayment Start Date]216 + ([Repayment Terms (in months)]216 * 30)

Thank you!

L_123

This one was kind of fun. I have a much more complicated solution, if you want I can break down exactly what this does, but in essence it uses a date formula and calculates the month and year separately and transposes the day from the original date. You should be able to put any integer in the repayment terms column and it will count it. Let me know if you have any issues.

=DATE(YEAR([Repayment Start Date]@row) + (MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row - MOD(MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row, 12)) / 12, MOD(MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row, 12), DAY([Repayment Start Date]@row))

L_123

Yeah all of december was out of wack. Apologies, that was a bit of an oversight. turned this into some crazy stuff, but it should be fixed now. Let me know if there are any issues with it.

=DATE(IF(MOD(VALUE(MONTH([Repayment Start Date]@row)) + [Repayment Terms (in months)]@row, 12) = 0, -1, 0) + YEAR([Repayment Start Date]@row) + (MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row - MOD(MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row, 12)) / 12, IF(MOD(VALUE(MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row), 12) = 0, 12, MOD(MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row, 12)), DAY([Repayment Start Date]@row))

L_123

I believe this will work for you:

=IF([Repayment End Date]@row < DATE(2021, 1, 31), 0, VALUE(MONTH([Repayment End Date]@row)) + 12 * (VALUE(YEAR([Repayment End Date]@row)) - 2021))

L_123

This one was kind of fun. I have a much more complicated solution, if you want I can break down exactly what this does, but in essence it uses a date formula and calculates the month and year separately and transposes the day from the original date. You should be able to put any integer in the repayment terms column and it will count it. Let me know if you have any issues.

=DATE(YEAR([Repayment Start Date]@row) + (MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row - MOD(MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row, 12)) / 12, MOD(MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row, 12), DAY([Repayment Start Date]@row))

Gabriela Colvis

Works perfectly, thank you! I just added -1 at the end of the formula to give me the last day of the previous month. Thanks again!

Gabriela Colvis

There were only about 5 line items that came up with this error.

L_123

Yeah all of december was out of wack. Apologies, that was a bit of an oversight. turned this into some crazy stuff, but it should be fixed now. Let me know if there are any issues with it.

=DATE(IF(MOD(VALUE(MONTH([Repayment Start Date]@row)) + [Repayment Terms (in months)]@row, 12) = 0, -1, 0) + YEAR([Repayment Start Date]@row) + (MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row - MOD(MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row, 12)) / 12, IF(MOD(VALUE(MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row), 12) = 0, 12, MOD(MONTH([Repayment Start Date]@row) + [Repayment Terms (in months)]@row, 12)), DAY([Repayment Start Date]@row))

Gabriela Colvis

That worked! Thank you! Are you up to helping with one more? Now we want to measure how many months are after Jan 2021 and that column is formatted as text/number.

Ideally, the first example below would equal 0 and the 2nd example would equal 1.

Thank you in advance!

L_123

I believe this will work for you:

=IF([Repayment End Date]@row < DATE(2021, 1, 31), 0, VALUE(MONTH([Repayment End Date]@row)) + 12 * (VALUE(YEAR([Repayment End Date]@row)) - 2021))

Cleversheet

Here's a generic solution for counting the months from today (or other referenced date)...

In column [Months from Today] this column-formula will show the number of months before/-after the value in [TestDate], where prior months are positive and future months are negative. If [TestDate] is in the current month the result is zero (0).

=IF([TestDate]@row < TODAY(), (VALUE(MONTH(TODAY()))) + (13 - VALUE(MONTH([TestDate]@row))) + ((VALUE(YEAR(TODAY())) - VALUE(YEAR([TestDate]@row)) - 1) * 12) - 1, (VALUE(MONTH(TODAY()) - 12)) + (-VALUE(MONTH([TestDate]@row))) + ((VALUE(YEAR(TODAY())) + 1 - VALUE(YEAR([TestDate]@row))) * 12))

To compare a given date against a particular date instead of against today, replace all instances of TODAY() with that date’s cell (or summary field) reference.

Note that this does not take into account the number days, only month values. Thus from 4/30/21 to 5/1/21 yields 1 "month" even there's only one day between those dates.