Formula to add Months to Date

heyjay

This is the simplest formula I can find to add Months to Date. This considers sum of months exceeding 12, converting it to January and adding 1 to the year.

Where: Term column is the number of months to add, Date is the starting date.

=DATE(
  YEAR([Date]@row) + INT((MONTH([Date]@row) + [Term]@row - 1) / 12), 
  MOD(MONTH([Date]@row) + [Term]@row - 1, 12) + 1, 
  DAY([Date]@row)
)

Posting it out here for my future use. I surely will forget this when I need it.

Paul Newcome

@heyjay This one will allow for more than just 12 months and can also handle negative months. I tested it out to +/- 60 with no errors, but I feel confident it will work for any number as long as it is within the date constraints on the back-end. I can't remember exactly what they are, but there is a date that is too far in the past to be recognized as a date and one in the future as well. As long as you stay within those (whatever they are), the below should work for you.

It is essentially an Excel "EDATE" replacement.

=DATE(YEAR([Original Date]@row ) + (FLOOR(MONTH([Original Date]@row ) + ([# of Months]@row - 1), 12) / 12), IF(MOD(MONTH([Original Date]@row ) + [# of Months]@row , 12) = 0, 12, MOD(MONTH([Original Date]@row ) + [# of Months]@row , 12)) + IF(IF(MOD(MONTH([Original Date]@row ) + [# of Months]@row , 12) = 0, 12, MOD(MONTH([Original Date]@row ) + [# of Months]@row , 12)) < 0, 12, 0), DAY([Original Date]@row ))