Count months in date range

KPH

I have a start date and an end date column.

i.e.

Start End

1/1/22 12/31/22

1/1/22 3/31/22

10/1/22 6/30/23

I need to know how many months fall into a certain time period

i.e. if the period was 7/1/22-6/30/23, the answer for the example above would be

Without a between operator, what is the best way to achieve this?

Find more posts tagged with

Accepted answers

All comments

Danielle Arteaga

=ROUND(((NETDAYS([Start Date]@row, [End Date]@row)) / 365) * 12, 0) (Placed in the [Months in Range] column in example below)

NETDAYS counts the numbers of days between your start date and end dates. That number, divided by 365 converts days into years. The year multiplied by 12 to convert years to months. ROUND gives you a whole number of months. The zero in the ROUND formula gives you whole months, but if you want fractional months, you can change the zero to whatever number of decimal places out you'd like to go.

KPH

Thanks Danielle

That gives me a neat way to calculate the number of months between two dates. I had been using =ROUND((([End Date]@row - [Start Date]@row) / 30), 0)

What I am stuck on is how to find the number of months in that range that also fall into a specific period. Ideally without too many nest IFs.

This works but I was hoping there was an easier way.

= IF(OR([End Date]@row < DATE(2021, 6, 30), [Start Date]@row > DATE(2022, 7, 1)), 0,

IF(AND([Start Date]@row > DATE(2021, 6, 30), [End Date]@row < DATE(2022, 7, 1)), ROUND(((NETDAYS([Start Date]@row, [End Date]@row)) / 365) * 12, 0),

IF(AND([Start Date]@row < DATE(2021, 7, 1), [End Date]@row < DATE(2022, 7, 1)), ROUND(((NETDAYS(DATE(2021, 7, 1), [End Date]@row)) / 365) * 12, 0),

IF(AND([Start Date]@row > DATE(2021, 6, 30), [End Date]@row > DATE(2022, 7, 1)), ROUND(((NETDAYS([Start Date]@row, DATE(2022, 7, 1))) / 365) * 12, 0),

"date range not supported"))))

For anyone else wanting to use the formula above, the first row above returns 0 if the task's end date is before my period starts or the start date is after if has ended.

The second row is the easy one for a task that is entirely within my period (using Danielle's method to count months between start and end).

The third row counts months for tasks that start before the period started and ends within the period (counting just months within the period).

The fourth row counts months for tasks that start during the period and end after the period ends (counting just months within the period).

I didn't bother with tasks that start before my period and end after as that won't happen if I keep my periods to one year. The little text message will deal with those, but could be replaced with another IF.

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.