I need to calculate the number of months within a year if that year falls within a date range. 10/1/2010 to 8/15/2017 how many months for each column of 2010, 2011, 2012,2013,2014,2015,2016,2017 so 2010 would return 3, the rest would return 12, and 2017 would return 8 months


May be an easier solution, but the following formula works :

=IF(AND(D$2 >= YEAR($B3);D$2 <= YEAR($C3));IF(D$2=YEAR($B3);12-MONTH($B3);IF(D$2=YEAR($C3);MONTH($C3);12));0)

With years on the second line, start dates in the B column and end date in the C column.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.