Using contract start/end dates and calculating annual contract day

[Redcon]

I'm doing a 5-year budget, from 1-1-08 through 12-31-12. At various times
during this period, contracts may start (but not necessarily end before end
of year 5). I need to know how many days a contract will be in effect during
each calendar year. For example, a contract starts 11-1-09 and ends 3-31-11.
Yearly contract days would be:
2008 0
2009 61
2010 365
2011 90

I would appreciate any suggestions for how I could go about obtaining these
results using Excel 2002.

Thanks.

 

[Pete_UK]

I put the years 2007 onwards in cells A21 to A28, and used C21 for the
start date and D21 for the end date. Then put this formula in B21:

=IF(OR(DATE(A21+1,1,1)<=C$21,DATE(A21,1,1)>D$21),0,IF(A21=YEAR(C
$21),DATE(A21+1,1,1)-C$21,IF(A21=YEAR(D$21),D$21-
DATE(A21,1,1)+1,DATE(A21+1,1,1)-DATE(A21,1,1))))

Copy this down column B to cover the years in column A. Play about
with different dates in C21 and D21 - it copes with leap years.

Hope this helps.

Pete

 

[Redcon]

Thanks so much, Pete!

Your solution works like a charm.

Redcon

 

[Pete_UK]

You're welcome, Redcon - thanks for feeding back.

Pete