Forum Discussion
Count number of months between two dates, but exclude certain months of the year.
Attaching a workbook with a possible solution. I have inserted some helper columns in order to make the formulae easier to follow. First, I determine the starting month in column C. Then the number of months between start date and end date, using the "DATEDIF" function in column D. Column E has the sum these two in order to be able to do the math that calculates the number of months that fall in the Oct-Dec period in column G. I have used a combination of MIN and MAX like this:
=MIN(MAX(0,E2-9),3)
MAX(0,E2-9) will determine if the months of Oct-Dec is included in the period examined. A value less than or equal to 0 indicates that the period does not include the months of Oct-Dec. A value greater than 0 indicates that it does. If the number is greater than 3, though, it will take 3 as the result. The latter part is achieved by the MIN(....................,3) portion of the formula.
Note that this formula will not work if the total period includes the Oct-Dec more than once. E.g. 1 July 2018 through 31 January 2020. Here you would want to see 6 as the result, since both 4th quarters of 2018 and 2019 are included. If this could occur in your situation, you need to tweak the formula a bit.
Attaching a workbook with a possible solution. I have inserted some helper columns in order to make the formulae easier to follow. First, I determine the starting month in column C. Then the number of months between start date and end date, using the "DATEDIF" function in column D. Column E has the sum these two in order to be able to do the math that calculates the number of months that fall in the Oct-Dec period in column G. I have used a combination of MIN and MAX like this:
=MIN(MAX(0,E2-9),3)
MAX(0,E2-9) will determine if the months of Oct-Dec is included in the period examined. A value less than or equal to 0 indicates that the period does not include the months of Oct-Dec. A value greater than 0 indicates that it does. If the number is greater than 3, though, it will take 3 as the result. The latter part is achieved by the MIN(....................,3) portion of the formula.
Note that this formula will not work if the total period includes the Oct-Dec more than once. E.g. 1 July 2018 through 31 January 2020. Here you would want to see 6 as the result, since both 4th quarters of 2018 and 2019 are included. If this could occur in your situation, you need to tweak the formula a bit.