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

BristolCVN

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!

Paul Newcome

Try a DATE function:

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

Paul Newcome

Try a DATE function:

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

BristolCVN

Thanks Paul, that worked! I also was able to add the IF statement and it works as well.

Paul Newcome

Happy to help. 👍️