Index match two date ranges
All,
Apologies should there have been a more appropriate title for this thread. Attached, I've provided a sample of the data with which I am working. Basically, the Activity tab contains 4 date timestamp milestones per record. The Calendar_Disruptors tab then contains a list of events with start and end dates that are considered disruptors as well as a list of US public holidays, also with start and end dates (the idea the end date may be updated to reflect additional days of observance).
I've included 'Disruptor', 'Holiday', and 'Weekend' column headers in the Activity tab along with my desired results in cell range J3:K6. I need to be able to write formulas to say if any date, including and between the 4 milestones of Activity correspond to a disruptor or holiday, then populate the corresponding field with the text value of said disruptor or holiday. Finally, if the date range includes a weekend, then populate the weekend column with 1, otherwise 0.
In a separate file, I was able to complete the disruptor column using index match with match type 1. Testing other ways to acheive the same results, I was also able to use sumproduct. However, I wasn't able to do the same for holidays and can't figure out for the life of me how to write a conditional statement for weekends.
Any assistance you may be able to offer would be most sincerely appreciated.
Thanks...Todd
|