murthy_v_s
Board Regular
- Joined
- Mar 2, 2007
- Messages
- 83
Hi -
I am not sure if the following timeline is good enough to represent my question (as it reformats the text)
12/21|--------|12/29
01/01|-------------|02/02
01/20|----------------|02/16
02/26|----------|03/01
12/24 |---------------------------------|02/04
Let say I have three columns A (ID), B (start date), C(end date). In the above example, everything is represented for ID = 12345 (An ID can be repeated any times and input data is not sorted).
12345 12/21/2009 12/29/2009
12345 01/01/2010 02/02/2010
12345 01/20/2010 02/16/2010
12345 02/26/2010 03/01/2010
12345 12/24/2010 02/04/2010
Given this data, I want to generate columns G, H (say) with ID (12345) and the count of days that are between (not sure if this is the right term) with the above five records. In the above example: days between (12/24 - 02/16) + days between (02/26 - 03/01).
A routine way is to write a vector with start, end dates (12/21/2009,12/22/2009, 12/23/2009,12/24/2009,12/25/2009,12/26/2009,12/27/2009,12/28/2009,12/29/2009) or (40168, 40169, .. etc,) UNION them and COUNT(DISTINCT). I dont know how to implement this in Excel and I would really appreciate any help.
Thanks a lot!!
I am not sure if the following timeline is good enough to represent my question (as it reformats the text)
12/21|--------|12/29
01/01|-------------|02/02
01/20|----------------|02/16
02/26|----------|03/01
12/24 |---------------------------------|02/04
Let say I have three columns A (ID), B (start date), C(end date). In the above example, everything is represented for ID = 12345 (An ID can be repeated any times and input data is not sorted).
12345 12/21/2009 12/29/2009
12345 01/01/2010 02/02/2010
12345 01/20/2010 02/16/2010
12345 02/26/2010 03/01/2010
12345 12/24/2010 02/04/2010
Given this data, I want to generate columns G, H (say) with ID (12345) and the count of days that are between (not sure if this is the right term) with the above five records. In the above example: days between (12/24 - 02/16) + days between (02/26 - 03/01).
A routine way is to write a vector with start, end dates (12/21/2009,12/22/2009, 12/23/2009,12/24/2009,12/25/2009,12/26/2009,12/27/2009,12/28/2009,12/29/2009) or (40168, 40169, .. etc,) UNION them and COUNT(DISTINCT). I dont know how to implement this in Excel and I would really appreciate any help.
Thanks a lot!!