identify which years are contained within a range of dates

[robindra]

Hi,

say I had some data where the cases have a start date and and end date. I need to know which years are covered by each case.

As each case can span several years I guess I need a column for each each year with a 'yes' and 'no' indicating whether the case included that year

so for example

start date end date 2008 2009 2010 2011
12/03/08 14/05/10 y y y n

it seems to be more complex than I thought, e.g 2009 would include all those cases with a start date in 2009, all those with an end date in 2009, but also any case where the start date is anytime before 2009 and an end date after 2009.

So basically I need a function that identifys if a range of dates is withing another range of dates. Does anyone know how?

I also have a secondary problem, once I have a year variable I also need to allocate how many days for each case fall within each year.

Ive put calender years for simplicity but really my data is financial years.

Any help would be much appreciated

thanks

[zbor]

Here, try this:

=IF(SUMPRODUCT(--(YEAR($A2)<=C$1),--(YEAR($B2)>=C$1))=0,"n","y")

[daddylonglegs]

Assume start date in A2, end date in B2 and years listed in C1 across.....then use this formula in C2 copied across

=IF(OR(YEAR($A2)>C$1,YEAR($B2)<C$1),"no","yes")

[robindra]

thanks, i've tried both formulas but cant seem to get it to work. I have start date in a2, end date in b2, and 2007 in c1, 2008 in d1, 2009 in e1 etc. Is this correct? I think it may not be treating the numbers as years but if I format it to date it will just give the number value of presumably the first say of that year

[daddylonglegs]

See the attached example

[robindra]

thank you, that is great, thanks again

[robindra]

I dont suppose anyone could help with my 2nd problem, a new set of columns for each year which shows how many of the days within the range occur in each year

e.g
start date, end date, 2007, 2008, 2009
01/01/07, 02/01/08, 365, 1, 0


thanks

[daddylonglegs]

Wouldn't that be 2 days in 2008?

With the same setup as previously try this formula in C2 copied across

=MAX(0,MIN($B2,DATE(C$1+1,1,0))-MAX($A2,DATE(C$1,1,1))+1)

[robindra]

thanks,

youre right 2008 should =2. I cant get the 2nd formula to work but think it might be because im using open office, hopefully it will work when I am back at work using excel tomorrow

thanks again