calculate days per year

3

3307

hello,

I have a number of contracts with different start and end dates. i would
like to calculate the number of days in each contract which fall within 2008,
2009, 2010, and 2011 respectively. (i.e. a column with the days in 2008, a
column with the days in 2009, etc.).

can anyone help me with the formula please?

many thanks.
e.
 
B

Bob Phillips

=DATE(2008,12,31)-start_date

=DATE(2009,12,31)-start_date-year_2008_sum

=DATE(2010,12,31)-start_date-year_2008_sum-year_2009_sum

etc.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
3

3307

Thank you Bob,
if I'm not mistaken your formula shows the number of days from the contract
start date to the end of a year.
I need to consider also the end date of the contract, i.e. when a contract
starts in 2007 and ends half way through 2009 i would want to see 365 days in
the 2008 column and 182 days in column 2009.

Can you help please?
thanks again.
 
B

Bob Phillips

Remiss of me, I should have considered that

=MIN(DATE(2008,12,31),end_date)-start_date

=MIN(DATE(2009,12,31),end_date)-start_date-year_2008_sum

etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
3

3307

thank you, i will give it a try.

Bob Phillips said:
Remiss of me, I should have considered that

=MIN(DATE(2008,12,31),end_date)-start_date

=MIN(DATE(2009,12,31),end_date)-start_date-year_2008_sum

etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
Top