Using contract start/end dates and calculating annual contract day
 

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.

Using contract start/end dates and calculating annual contractday
 

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.

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.

Using contract start/end dates and calculating annual contract
 

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

Pete

On Apr 18, 11:58*pm, Redcon wrote:
Thanks so much, Pete!

Your solution works like a charm.

Redcon