Calculate amount of days per year within range

Hi all,

I would be very glad if someone could help me figure out the following:

On the basis of the two column A (startdate of project) and B (enddate of project) I would like to calculate the amount of days within a given year in the range between A and B to calculate the amount of costs on that basis. If come to the point where I have a ridiculous amount of DATEIF formulas based on the premise that the one formula has to be able to deal with projects starting mid-year. Also, as I am dealing with project for the next ten years starting dates and ending dates may overlap.

In short: I can calculate column C (DATEDIF) but what I am looking for is a formula to calculate the amount of days in each year within the range of a given project. So for row1; amount of days in 2015, amount of days in 2016 and amount of days in 2017.

For instance with the following data:


A(start project) B(end project) C(total days)
1 01-01-2015 01-01-2017
2 01-01-2019 01-06-2021
3 01-06-2016 01-06-2017

Really appreciate all the help I can get here) Thanks!!

Kind regards,

Bart

Works like a charm mate! Thanks! Had to built in a IF to remove the negative values but I think I worked it out!

Many thanks.