How to add months to date?

tblev

I am trying to calculate a clawback expiration date from the date the agreement was signed and the duration of the clawback in months and can't figure out a Smartsheet formula. In Excel the EDATE formula works great. Can someone please help me with this formula in Smartsheet?

KPH

Hi @tblev

How do you define a month? Is it a calendar month or a 12th of a year?

If you are using 1/12 of a year you could add 30.4375 days multiplied by the number of months. So April 11, 24 plus 24 months would be plus 24 x 30.4375 days (730.5 days) which would be April 11, 24. However, some expirations might not fall exactly on the same day due to leap years, and short months. For example, 3 months from 1st Feb 2022 would be 3rd May 2022.

If that is how you figure months you can multiply the number of months by 30.473 and then add it to the date. Both date columns must be date format.

=[Date Agreement Signed]@row + ([Clawback Duration in Months]@row * 30.4375)

If you are using calendar months and the expiration date needs to be the same day of the month as the agreement is signed, it is a slightly more complex formula as you will need to add the number of months to the MONTH() part of the date using:

=MONTH([Date Agreement Signed]@row)+24

But also handle situations where the month number plus the duration is a number more than 12. If this is something you want to do, can you let us know the maximum duration you will be adding.

KenR

My solution was to create a helper sheet defining how many days are in each month. To account for leap years, I formatted my table with the first day of each month. (I used Excel to generate the list of dates and Days in Month for the next 10 years.)

Then in my sheet, I used the "sumifs" function, referencing my helper sheet, to sum days in month. The first criteria sets the lower date limit. The second criteria sets ending date limit. To get the ending date limit I used index/match where match is offset by the duration of months desired.

=SUMIFS({Days in Month}, {Days in Month MonthDate}, >=DATE(YEAR(DateColumn@row), MONTH(DateColumn@row), 1), {Days in Month MonthDate}, <INDEX({Days in Month MonthDate}, MATCH(DATE(YEAR(DateColumn@row), MONTH(DateColumn@row), 1), {Days in Month MonthDate}, 0)+ Duration@row))

Then I add the number returned from this formula to the start date. This permits the native functionality of Smartsheets, adding days to dates, to handle multiple year situations.

=[Starting Date]@row + [Days to add]@row

here's the explanation of the sumifs formula:

=SUMIFS({Days in Month} //Range to sum
**** first criteria ****
, {Days in Month MonthDate} // {Days in Month MonthDate} is the helper sheet reference to Month column
, >=DATE(YEAR(DateColumn@row), MONTH(DateColumn@row), 1) // date formula to find the first day of month for the original date
**** second criteria ****
, {Days in Month MonthDate}
, <INDEX({Days in Month MonthDate} // returns the corresponding date for the index returned by match
, MATCH(DATE(YEAR(DateColumn@row), MONTH(DateColumn@row), 1), {Days in Month MonthDate}, 0) // matches the first day of month for the original date
+ Duration@row)) // adds the number of months desired to the index of the original date