Counting the number of days of a project during each year

[sagan97]

Hi all,

I need a formula that brings me the number of days of each year where a project was active. For instance, if a project starts on February1, 2012, and finishes on April 30, 2015, I want columns to be populated with the number of days that project was active during 2012 ( 334 days ) 2013&2014 ( 365 days each) ; 2015 ( 119 days ). Something like in the below screenshot. Which is the most suitable formula to do it?

Many thanks! 

[#2]

@sagan97 

Your calculations are not quite correct. For example, in 2015, January 1 to April 30 is 31+28+31+30=120 days.

See the attached workbook.

[#3]

Many thanks Hans for your answer, really helpful.

One more thing:
If I want to settle a calendar different from the natural calendar. For instance, I want that everything from June 1, 2010 to May 31, 2011 falls under "Period2010"; June 1, 2011 to May 31, 2012 -> "Period 2011" ... ? May I mannually change that?
Thanks again!

[#4]

@sagan97 

See the attached modified version.

[#5]

@Hans Vogelaar 

This is super helpful...thank you! I'd like to have the worksheet count days in the total and per calendar year if the project end date is empty. Can you modify your file to deal with that?

Edit: For the Total, it should be =IF(ISBLANK(C3),(TODAY()-B3)+1,C3-B3+1), but having difficulty with the year by year amounts.

Thanks in advance!

[#6]

@barak1948 

See the attached version.

[#7]

@Hans Vogelaar 

Thank you for the quick response! Posting this for others looking at this:

I didn't need the prior users need to accommodate strange start and end date of years (he wanted the year specified as 6/1 - 5/31). The version you attached kept that. Switching the formula in cell F3 to:

=MAX(MIN(DATE(F$2,12,31),IF($C3="",TODAY(),$C3))-MAX(DATE(F$2,1,1),$B3)+1,0)

and copying to the other cells got it back to the "normal" 1/1 - 12/31 calendar year cycle.

[#8]

@barak1948 You saved my a$$ internet stranger - I thank you.