Calculate number of days per month within a date range that spans more than one year

I have a schedule that spans (2) calendar years. I want to return the number of days per month included in the range. Since I use this result for billing purposes, I have a column per month and year of the schedule the returns the number of days in that given month/year. I am running into problems once I get to the same month of the following year.

I used the following formula successfully to manage the first 12 months of the schedule; however, when you get to the same month in the following calendar year it fails to recognize the difference between the months of different years:

=IFERROR(VALUE((YEAR([End Date]@row) - YEAR([Start Date]@row)) * IF(AND([End Date]@row - [Start Date]@row < 365, OR(MONTH([End Date]@row) = 8, MONTH([Start Date]@row) = 8)), 0, 31) + IF(AND(OR(MONTH([Start Date]@row) < 8, YEAR([Start Date]@row) < YEAR([End Date]@row)), MONTH([End Date]@row) = 8), DAY([End Date]@row), IF(AND(MONTH([Start Date]@row) = 8, MONTH([End Date]@row) = 8), DAY([End Date]@row) - DAY([Start Date]@row), IF(AND(MONTH([Start Date]@row) < 8, MONTH([End Date]@row) > 8), 31, IF(AND(MONTH([Start Date]@row) = 8, OR(MONTH([End Date]@row) > 8, YEAR([End Date]@row) > YEAR([Start Date]@row))), 31 - DAY([Start Date]@row), ""))))), "")

How can this be modified to add in the year so that as it carries across future months of a different year that it recognizes the difference between a certain month that falls in one year or the other? It seems that an additional condition can be placed to capture if the year is within YEAR("0000") but I am having trouble making it work.

MANY THANKS IN ADVANCE!!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!