HELP!! Calculating capacity by year

[regionrat63]

Hi all! Currently having a problem calculating capacity by date for a spreadsheet.

I have a start and finish dates for people and I want to calculate capacity by year for that person. So for example, if they started 3/17/2014 and left the company on 7/27/2015, I need a formula that can look at those dates and calculate capacity for 2014 (3/17/2014 - 12/31/2014) and then another column that has capacity for 2015 (1/1/2015-7/27/2015). The set capacity is 1000 hours a year.

I used YEARFRAC to get their total year in decimal form to multiply by the 1000 hours a year, but I was not able to find out a way to separate it by individual years for individual year columns.

Thanks!

[JohnnyEnglish750]

attach the file so someone could better help you

[regionrat63]

Here is the attached basic file explaining what I am looking to do

[regionrat63]

bumping this ^

[JohnTopley]

In D3

=IF(D$1=YEAR($A3),YEARFRAC($A3,DATE(D$1,12,31)),IF(D1=YEAR($B3),YEARFRAC(DATE(D$1,1,1),$B3),IF(D$1>YEAR($B3),0,1)))

Row has years without text i.e 2014, 2015 etc

[regionrat63]

Can you explain the reasoning in this formula to me?^^

[JohnTopley]

=IF(D$1=YEAR($A3),YEARFRAC($A3,DATE(D$1,12,31)),IF(D1=YEAR($B3),YEARFRAC(DATE(D$1,1,1),$B3),IF(D$1>YEAR($B3),0,1)))

The RED section checks if the start year = year in D1: if True, the YEARFRAC is start date to 31 dec of that year

The Green checks if the year is the finish year: if true, then yearFRAC is from Jan 1st to End date.

The last part checks if we are past the end year date: if True, st to 0, otherwise it must be a whole year so set to 1,

Reviewing the formula I added another test ...

=IF(D$1 < YEAR($A3),0,IF( D$1=YEAR($A3),YEARFRAC($A3,DATE(D$1,12,31)),IF(D1=YEAR($B3),YEARFRAC(DATE(D$1,1,1),$B3),IF(D$1>YEAR($B3),0,1))))

If Start Year > D1, set to 0.