Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Contract/Notice Dates | Excel Discussion (Misc queries) | |||
Calculating earliest start and latest end dates by contract | Excel Worksheet Functions | |||
variable annual wage increases based on start date | Excel Worksheet Functions | |||
MAKING A CONTRACT FROM DATA FROM A WORKSHEET | Excel Worksheet Functions | |||
MAKING A CONTRACT FROM DATA FROM A DIFFERENT FILE | Excel Worksheet Functions |