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!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try a DATE function:

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

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.