Forum Discussion
Count number of months between two dates, but exclude certain months of the year.
I have yet to find a solution after hours of search. Seems like it would be very simple, just can't figure it out. I need to calculate storage costs for inventory stored in a warehouse, however between Oct-Dec storage costs are much higher. My goal is to take a Start and End Date to determine how many months fall withing Sep-Jan and how many fall between Oct-Dec and then multiply them by the cost of storage during that month.
For Example:
Arrival Date is 8/30/2019. I want to calculate the total cost to store inventory from the date it arrived to Today. Once I find the how many months fall between Jan-Sep and how man fall between Oct-Dec I can multiply those number by their monthly storage cost and them all up. The problem is figuring out how many months fall between my low cost months and high cost months as in the table below.
| Start Date | End Date (Today) | Months In Storage(JAN-SEP) | Months In Storage(OCT-DEC) |
| 11/1/2018 | 10/31/2019 | 9 | 3 |
| 8/1/2019 | 10/31/2019 | 2 | 1 |
Thank you in advanced!
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.
4 Replies
This solution utilizes the new function SEQUENCE.
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.
