Tricky formula: Splitting two dates in its count of days per calendar year

[actjfc]

Excel friends,

I have a table with two columns Start Date and End Date. For example,
Start Date - End Date
7/1/2014 - 6/30/2017

I need help developing a formula that counts the days between these two dates split by the calendar year.

So, I built six columns with the calendar year to count the days.

These should be the results of the formula:

2013 - Results 0
2014 – Days from 7/1/2014 to 12/31/2014 = Results 183
2015 - Results 365
2016 - Results 365
2017 – Days from 1/1/2017 to 6/30/2017 = 180
2018 - Results 0

It becomes tricky when you have two dates like:

Start Date - End Date
10/15/2015 - 11/14/2015

These should be the results of the formula:

2013 - Results 0
2014 –Results 0
2015 - Results 30
2016 - Results 0
2017 – Results 0
2018 - Results 0

Thanks for your help!

 

[soadfan]

It's a long shot, and i didn't test it, but i guess you can parse "date - date" with len(), left()/right(), find() and use datevalue() & year() to extract the year, then sumif() to get days or something like that.
But why bother, can you use helper columns and concatenate ?

 

[actjfc]

Thanks!

I found what I was looking for:

Distributing All Days Across Years

=MAX(0,(MIN($C39,DATE(D$38,12,31))-MAX($B39,DATE(D$38,1,1))+1))

A full explanation is in http://www.cpearson.com/excel/distribdates.htm