Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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!

Trending in Formulas and Functions