0

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]])}

Photo of table with formula working incorrectly

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?

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
mjp
  • 99
  • 1
  • 12
  • 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](https://support.office.com/en-us/article/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e) to get a better understanding of structured references. – TotsieMae Sep 07 '18 at 16:56
  • @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 Sep 07 '18 at 17:03
  • In what order do you have your holidays on **SLA Holidays**? Would it be possible for you to show us the dates? – TotsieMae Sep 07 '18 at 17:18
  • Also, double-check to make sure your holidays on **SLA Holidays** are in the correct year and formatted as dates. – TotsieMae Sep 07 '18 at 17:21
  • @TotsieMae The dates on the **SLA Holidays** tab are in ascending order and formatted as Short Dates. They are the correct year as well. I don't think I can include another picture, it keeps replacing the first one. – mjp Sep 07 '18 at 17:27
  • @TotsieMae also, after reading up on that site you linked to me, the @ symbols specify that I want the value in _This Row_ of the column I'm referencing. I tried using a direct cell reference instead of the [@[column]] format and ran into the same issue. – mjp Sep 07 '18 at 17:29
  • I'm not sure why it keeps replacing the image when you try to add it. However, I just tried to recreate your problem on my end using Excel 2016 and I couldn't. Your formula seemed to work well with a set of holidays. – TotsieMae Sep 07 '18 at 17:40
  • 1
    @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 Sep 07 '18 at 17:49
  • 1
    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 Sep 07 '18 at 18:19

1 Answers1

1

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.

mjp
  • 99
  • 1
  • 12
  • 1
    FWIW, the following formula will tell you how many holidays fall within the date range: `{=(12-SUM(ABS(SIGN('SLA Holidays'!$A$2:$A$7-VALUE([@[Start Date]]))+SIGN('SLA Holidays'!$A$2:$A$7-VALUE([@[End Date]])))))/2}` The magic number 12 is double the number of rows in the holiday table. I leave figuring out the rest as an exercise for you if you want it. ;-) – StoneGiant Sep 07 '18 at 18:26
  • Bonus points if you can add in to check if the holiday is on a weekday (1 if there's 1 holiday during the week, 0 if it's on the weekend) – mjp Sep 07 '18 at 19:05
  • 1
    Heh. Yeah, no thanks. If it were on my plate though, I would be looking at something like `WEEKDAY($A$2:$A$7,2)<6` as a starting point. But, a difficulty arises if more than one holiday happens to fall inside the date range. Good luck! – StoneGiant Sep 07 '18 at 20:18
  • 2
    Ya know... I think I have a problem. I just couldn't resist. The following gives you the total number of weekday holidays in range **Start Date** to **End Date** for a list of holidays in *'SLA Holidays'!$A$2:$A$7* `{=SUMPRODUCT(1-ABS(SIGN('SLA Holidays'!$A$2:$A$7-VALUE([@[Start Date]]))+SIGN('SLA Holidays'!$A$2:$A$7-VALUE([@[End Date]])))/2, -- (WEEKDAY('SLA Holidays'!$A$2:$A$7,2)<6))}` The formula now no longer cares about the size of the table. In fact, I _think_ the holiday range could be replaced by a named range. – StoneGiant Sep 07 '18 at 21:02
  • 1
    Here's the formula using a named range and corrected for a problem that was happening if the holiday fell on a start or end date. `{=SUMPRODUCT(ROUND(1-ABS(SIGN(CompHolidays-VALUE([@[Start Date]]))+SIGN(CompHolidays-VALUE([@[End Date]])))/2,0), -- (WEEKDAY(CompHolidays,2)<6))}` ... Okay, seriously... stopping now. – StoneGiant Sep 07 '18 at 21:13