Using contract start/end dates and calculating annual contract
Thanks so much, Pete!
Your solution works like a charm.
Redcon
"Pete_UK" wrote:
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
On Apr 18, 8:40 pm, Redcon wrote:
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.
|