Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Quote:
I did a formula for you. Take a look at the example in attachment. Please, tell me if it worked for you.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
|
|||
|
|||
thank you, it looks good, i'll try it out when im back at work later, thanks
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How Determine If 2 Dates Are Within Range Of Years | Excel Worksheet Functions | |||
How to verify if a range (or a cell) is contained in another range | Excel Programming | |||
How do I subtract dates to get a number in years or years & month | Excel Discussion (Misc queries) | |||
Identify a range of cells containing dates | Excel Worksheet Functions | |||
count number of years 2003 in a range of dates | Excel Worksheet Functions |