Formula Referencing Excel Table

Question

I am using Excel 2013.

I have a table with three columns: Start Date, End Date, and Holiday - a column containing a formula to check if a holiday falls between the Start Date and End Date. The Start Date and End Date columns contain references to other columns where the date is calculated. The formula in the Holiday column uses an Index/Match function to look in a list of holidays (on a separate sheet called SLA Holidays). This table has thousands of records ranging over a one-week time span, and is updated daily.

The holiday formula does not work - it returns FALSE even when there is a holiday in the time frame. Upon evaluating the formula, it says that the first logical of the AND function is FALSE; however, it is true (9/3/2018 > 8/31/2018). The formula correctly evaluates the second logical of the AND function.

When I replace my table references to Start Date and End Date with references to cells outside of the table containing those dates, the formula works correctly.

Below is a screenshot of a few records from the table, showing the 3 columns. I've also included an HTML version for copy-ability. The formula is as follows:

{=AND(INDEX('SLA Holidays'!$A$2:$A$7,MATCH(MIN(ABS('SLA Holidays'!$A$2:$A$7-[@[Start Date]])),ABS('SLA Holidays'!$A$2:$A$7-[@[Start Date]]),0))>[@[Start Date]],INDEX('SLA Holidays'!$A$2:$A$7,MATCH(MIN(ABS('SLA Holidays'!$A$2:$A$7-[@[Start Date]])),ABS('SLA Holidays'!$A$2:$A$7-[@[Start Date]]),0))<[@[End Date]])}

Start Date  End Date    Holiday
08/31/2018  9/4/2018    FALSE
08/31/2018  9/4/2018    FALSE
08/31/2018  9/4/2018    FALSE

When I use the exact same formula, but switch the table references with cell references containing the same dates, the formula works. Here is that updated formula:

{=AND(INDEX('SLA Holidays'!$A$2:$A$7,MATCH(MIN(ABS('SLA Holidays'!$A$2:$A$7-AC5)),ABS('SLA Holidays'!$A$2:$A$7-AC5),0))>AC5,INDEX('SLA Holidays'!$A$2:$A$7,MATCH(MIN(ABS('SLA Holidays'!$A$2:$A$7-AC5)),ABS('SLA Holidays'!$A$2:$A$7-AC5),0))<AD5)}

Is there a way to make this formula work with references to dates within the table?

Welcome, @mjpaley10! Have you tried evaluating this formula? Go to the Formulas tab, and in the Formula Auditing group, click Evaluate Formula to step through the calculation process. I believe the underlying issue is related to it being an array formula and your formula referencing the Start and End Dates with @. Take a look at this site to get a better understanding of structured references. — TotsieMae
@TotsieMae I edited the post to add detail about when I evaluated the formula. Why would it correctly evaluate one part of the formula but not another? — mjp
In what order do you have your holidays on SLA Holidays? Would it be possible for you to show us the dates? — TotsieMae
@TotsieMae I figured out the source of the error! The Start Date and End Date columns contain references to other columns that calculate the dates, not just the actual date value. When I switch it to the actual date value, the formula works. Thank you so much for the help! — mjp
I was playing around with your formula and noticed that it does not give the correct answer in all date range cases, even when it is working correctly. Assuming your Holidays include Independence Day (7/4/18) and Memorial Day (9/3/18) US Holidays, a start date is 7/5/18 with an end date of 9/5/18 is reported as FALSE, while 8/31/18 to 9/5/18 is reported as TRUE. That seems like potential for a hidden bug. — StoneGiant

mjp mjp · Accepted Answer · 2018-09-07T18:17:49

The Start Date and End Date columns contained formulas to calculate the dates, not just the actual date value. I changed my Holiday formula to include the Value function, taking the value of the table column references, and this solved the error.

Formula Referencing Excel Table

1 Answers