Formulas to differentiate overlapping break data

Question

I have a mass amount of data from our ACD system where I am trying to identify who is the second (or third, fourth, etc. person) on break and how many times in one day they go as the second person. I have already narrowed down the general overlaps by using =SUMPRODUCT((B2<endtime)*(C2>=starttime))>1 but need to further narrow down the name and frequency of the repeating offender without having to manually count. So, in the example below, I would like to know that Michael was the 2nd on Break 2 times in one day but that Sally was never the second on break.

Another problem I am having is that the data that is pulled in by our reporting system sometimes breaks up a single break time into two line items (see Nancy in example below). If possible, I would also like for the second line item's End Date Time to replace the first line item's End Date Time so the duration calculates correctly.

Any thoughts or suggestions on making this happen, if possible, would be appreciated!

Answer 1

Hi

Thank you for posting the thread on our forum and we also appreciate you spent your precious time in here.

Based on your description, it seems that you want to know some formula about how to Find overlapping date ranges. (If my understanding is incorrect, please feel free to post back).

If my understanding is correct, you can use solution with SUMPRODUCT. The formula itself:

=IF(SUMPRODUCT(([@ValidFrom]<=[ValidTo])*([@ValidTo]>=[ValidFrom])*([@ProductID]=[ProductID])*([@CustomerCode]=[CustomerCode])*([@PriceType]=[PriceType]))>1,”OVERLAP”,”OK”)

The MS support page defines SUMPRODUCT function as “The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays.”. Let’s see step by step how it is working in this particular case.

We are using 4 criteria in our formula, one for each of the following fields: ValidTo, ValidFrom, ProductID and CustomerCode. The criteria are in parenthesis and separated by an asterisk ‘*’.

Each criteria is evaluated separately and returns TRUE (1) or FALSE (0). If you select one of the criteria in the formula bar and press F9 you can see the results:

In ‘human’ language we ask the formula please sum those rows in the data set where:

1. the actual row’s ValidFrom is less then or equal to ValidTo
2. the ValidFrom is less than or equal to actual row’s ValidTo
3. the ProductID and the CustomerCode and the PriceType are identical with the actual row’s fields

In more detail:

Our SUMPRODUCT in the above example returns 3 therefore 3 out of 4 lines are overlapping. I nested the SUMPRODUCT into an IF function and since 3 is greater than 1 the final result is OVERLAP.

Hope that you can find the this formula useful and of course feel free to customize it for your own needs.

If there have any unclear or misunderstanding, please feel free to post back and we’ll continue to help you all the time!

Your understanding and patience will be highly appreciated! Hope you have a good day and keep safe!

Best regards,

Stacey