Calculate the # of months between 2 dates, with 1 date being static

Hi all,

I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my sheet, and 2) 12/1/22 as a static date in every calculation.

So far I have been able to create a basic formula that works, however, I had to create another column (Calc Date) to put 12/1/22 to reference in the formula. Ideally, I want to use the formula as a "Column Formula" so that any new entries will apply the same formula, but using absolute references is a limitation.

This is what I have so far:

=((NETDAYS([Requested Start Date]@row, $[Calc Date]$1) / 365) * 12)

I'm guessing I will also need to add an "IF" statement for entries where the Requested Start Date is greater than 12/1/22, because the formula above will show "- #" for entries based on what I have so far.

Thanks in advance!


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!