• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dates array overlap calculation

K

kettlebell

New Member
Thanks in advance for your help. I searched forum, but didn't find answer.

I need to calculate total overlap in array:

Start Date; Finish Date; Marker; Range

I have a range, let say one week, and I need to know how many activities with maker fall into range, or overlap with it, and for how many days in total. There are more than 1500 positions in first two columns, about half of them have marker, and I need to check about 200 ranges.

Thanks again.
 
Hi kettlebell,


Welcome to the forum, What do you mean by marker?? For every Range you have start and a finish date and you would like to check for marker whether it is within that bracket of dates or not?? How is your date formatted ( a text or as date)?? Am i correct? Waiting for your clarification.


Regards,

Faseeh
 
All dates formatted as dates, not a text. Marker field for each activity could have a letter "y" or remain blank. My question probably easy to explain on example: Let say I have 1500 activities with start and finish dates distributed over 3 years period. and 500 of them have marker "y" in corresponding cell. I want to check how many activities with marker fall into range, let say from January 1st 2012 to January 12 2012 and by how many days in total. Because some of them could start earlier than Jan 1st and continue further than Jan 12, so it would be 12 days overlap; some could start on Jan 5 and not finish at Jan 12 - overlap 7 days; and so on.


Thanks
 
Hi see this file:


http://dl.dropbox.com/u/60644346/Overlapping%20dates..xlsx


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top