Index match two date ranges

[toddkad]

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

[xor]

Could you possibly upload the separate file in which you was able to complete the disruptor column?

[toddkad]

Sure, xor and thanks for the reply. File attached. I was able to use sumproduct in columns AT and AU.

[xor]

Does the formula in sheet Activity, column E return what you expect?

Could you please try to fill in manually some expected values in column F and G.

Can it happen that more holidays appear in a single row on sheet Activity (column a:d)
If YES, what should be returned?

[toddkad]

Hi xor,

Thanks for the reply. Per your request, I've entered some manual values into columns F and G. There shouldn't be any instances in which a date range in a row between columns A should contain more than one holiday as separately, I'll be building in an exception column that will flag any record where worked-arrival is greater than 14. (don't believe there are any US holidays that occur within 2 weeks of each other).

As for the formula in column E, this is great however, it does return incorrect values when column D is blank.

Thanks again for your help. I was stuck thinking that I had to use INDEX MATCH SUMPRODUCT and not just INDEX SUMPRODUCT. That alone was a great learning experience.

[xor]

I don't think I fully understand the problem.

In the file ExcelForum DateRangeArrays_3, sheet Activity cell C12, "Plant down" is returned which you say is incorrect. What should be returned then?

If I look at the same row in file Forum_Test I can see that your SUMPRODUCT function in AT12 returns 3 which is the same as my SUMPRODUCT function returns (ExcelForum DateRangeArrays_3, sheet Activity cell C12).

Can you please try again to explain exactly what determines which text should be returned.

[toddkad]

Sorry, xor and thanks for the continued replies. "Plant down" is the value that should be returned when the date range, in this example, from A1212 falls within the date range that the plant was down (9/3-9/13). However, the date values in cells A12:C12 are 10/10-10/16 in which case, then "ASN data issues" should have been returned as that is the date range that contains dates between 9/14-10/31.

[xor]

I don't have more energy today.

Will take a fresh look at it tomorrow, but I will not promise I can come up with something useful.

[toddkad]

Thanks. I don't think I've ever been this stumped by anything in Excel. No matter how I approach this, I continue to get inconsistent results. Appreciate your support.

[xor]

I have tried again, column E and F in sheet Activity and have made some defined names, see Name Manager. The formula in column F is not mine but is due to a person named Amit Tandon.

Not sure if this gives you what you need, but you may want to take a look at it.