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.
Riny_van_Eekelen Thank you! Didn't even consider using MIN/ MAX functions. I was able to come up with a solution that also accounts for units stored up to 3 years back. Thanks again! Really appreciate this
You're welcome. Glad I could inspire you to fix the problem. Even though I made a mistake in my formula to count the number of months in column D. Should have added "+1". You obviously discovered that the DATEDIF function gives different results when you compare dates from e.g. April 1 to December 1 or from April 1 to November 30. In both cases you expect 8 months, but you need to add the "+1" if your period goes from the beginning of one month to the end of another.
