Microsoft Office Forums Index match two date ranges

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-28-2016, 07:32 PM
toddkad toddkad is offline Index match two date ranges Windows 7 64bit Index match two date ranges Office 2013
Novice
Index match two date ranges
 
Join Date: Oct 2016
Posts: 5
toddkad is on a distinguished road
Red face 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
Attached Files
File Type: xlsx ExcelForum_DateRangeArrays.xlsx (78.0 KB, 4 views)
Reply With Quote
  #2  
Old 10-30-2016, 12:32 AM
xor xor is offline Index match two date ranges Windows 10 Index match two date ranges Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Could you possibly upload the separate file in which you was able to complete the disruptor column?
Reply With Quote
  #3  
Old 11-01-2016, 12:50 PM
toddkad toddkad is offline Index match two date ranges Windows 7 64bit Index match two date ranges Office 2013
Novice
Index match two date ranges
 
Join Date: Oct 2016
Posts: 5
toddkad is on a distinguished road
Default Index match two date ranges

Sure, xor and thanks for the reply. File attached. I was able to use sumproduct in columns AT and AU.
Attached Files
File Type: xlsx Forum_Test.xlsx (443.5 KB, 3 views)

Last edited by toddkad; 11-01-2016 at 12:52 PM. Reason: forgot attachment
Reply With Quote
  #4  
Old 11-02-2016, 03:59 AM
xor xor is offline Index match two date ranges Windows 10 Index match two date ranges Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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?
Attached Files
File Type: xlsx ExcelForum_DateRangeArrays_2.xlsx (110.1 KB, 4 views)
Reply With Quote
  #5  
Old 11-02-2016, 10:30 AM
toddkad toddkad is offline Index match two date ranges Windows 7 64bit Index match two date ranges Office 2013
Novice
Index match two date ranges
 
Join Date: Oct 2016
Posts: 5
toddkad is on a distinguished road
Default

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.
Attached Files
File Type: xlsx ExcelForum_DateRangeArrays_3.xlsx (110.5 KB, 1 views)
Reply With Quote
  #6  
Old 11-02-2016, 11:53 AM
xor xor is offline Index match two date ranges Windows 10 Index match two date ranges Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #7  
Old 11-02-2016, 12:05 PM
toddkad toddkad is offline Index match two date ranges Windows 7 64bit Index match two date ranges Office 2013
Novice
Index match two date ranges
 
Join Date: Oct 2016
Posts: 5
toddkad is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 11-02-2016, 12:50 PM
xor xor is offline Index match two date ranges Windows 10 Index match two date ranges Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #9  
Old 11-02-2016, 05:23 PM
toddkad toddkad is offline Index match two date ranges Windows 7 64bit Index match two date ranges Office 2013
Novice
Index match two date ranges
 
Join Date: Oct 2016
Posts: 5
toddkad is on a distinguished road
Default

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.
Reply With Quote
  #10  
Old 11-03-2016, 03:10 AM
xor xor is offline Index match two date ranges Windows 10 Index match two date ranges Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Attached Files
File Type: xlsx ExcelForum_DateRangeArrays_5.xlsx (136.8 KB, 3 views)
Reply With Quote
Reply

Tags
excel 2013 formula

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - Index and Match Function ,First Second and Third Match paulzy95 Excel 10 09-29-2016 10:46 PM
Translate INDEX(MATCH) into VBA grexcelman Excel Programming 1 03-08-2015 05:06 PM
Index match vba grexcelman Excel 0 03-05-2015 10:55 PM
Index Match syntax grexcelman Excel Programming 0 03-05-2015 07:17 AM
UDF multiple IFs INDEX-MATCH grexcelman Excel Programming 0 02-22-2015 04:20 PM


All times are GMT -7. The time now is 04:38 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft