What is the formula to determine the number of months that overlap between two date ranges?

jrgreen

I need help getting a formula to work for figuring out how many months are overlapping for project with a start and end date within a fiscal year start and end date. Here is the formula that I am attempting to use. Please help!!!

=IF(AND([End Date Planned]@row >= [FY25Start Date]@row, [Start Date Planned]@row <=[FY25 End Date]@row), DATEDIF(MAX([Start Date Planned]@row, [FY25Start Date]@row, MIN([End Date Planned]@row, [FY25End Date]@row, "M") 0)))

I'm pretty sure that it has problems with the parenthesis.

rrenee

Hi,

So immediately when looking over this, you have "DATEDIF" which is from Excel, but there is unfortunately not an equivalent function in Smartsheet.

To find overlapping months, I think you'll have to do something a bit different:

=IF(AND([End Date Planned]@row >= [FY25Start Date]@row, [Start Date Planned]@row <=[FY25 End Date]@row), MONTH(MAX([Start Date Planned]@row, [FY25Start Date]@row)) - MONTH(MIN([End Date Planned]@row, [FY25End Date]@row)), 0)

This finds the difference in month number of the max start and min end dates similar to how you wrote it previously. If there is overlap between years in January or December, it may need an adjustment as Month returns 1-12 regardless of year.

If you're curious about just the difference in days, remove the MONTH():

=IF(AND([End Date Planned]@row >= [FY25Start Date]@row, [Start Date Planned]@row <=[FY25 End Date]@row), MAX([Start Date Planned]@row, [FY25Start Date]@row) - MIN([End Date Planned]@row, [FY25End Date]@row), 0)

This may be helpful in counting for overlap if the number of days is greater than 31.

I hope this helps~