identify which years are contained within a range of dates
 

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

Dear robindra, Good Evening.

I did a formula for you.

Take a look at the example in attachment.
Please, tell me if it worked for you.

identify which years are contained within a range of dates
 

On Wed, 2 May 2012 19:56:29 +0000, robindra wrote:


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


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+

I'm sure there are logical formulas that can do this, but here is a "brute force" method that will work in versions of Excel prior to 2007 up to 5 June 2079. In versions 2007 and later, it should work up through 25 November 4770.


A2: Start Date
B2: End Date
C1: 2008
D1: 2009
etc

For the Y/N return if there are any dates in the particular year entered as a number into row 1:,

=IF(SUMPRODUCT(--(YEAR(ROW(INDIRECT($A2&":"&$B2)))=C$1))0,"Y","N")

For the count of the number days in the particular year:

=SUMPRODUCT(--(YEAR(ROW(INDIRECT($A2&":"&$B2)))=C$1))

And just fill right the appropriate number of columns.

thank you, it looks good, i'll try it out when im back at work later, thanks