Identify Overlapping Times with Multiple Criteria

DMDGUSA

New Member
Joined
Mar 26, 2019
Messages
6
I've been struggling to find a way to identify pairs of entries for which at least of portion of their time overlaps. To make the challenge more difficult, I need potentially overlapping entries to meet multiple criteria before they are determined to be overlapping.

I have two fields for identifying overlapping entries. One is an "Has Overlap" field which should simply return TRUE or FALSE if an entry shares a time overlap with another entry and meets all the criteria. In the second field, for every pair of entries which overlap and meet the multiple criteria, I need each entry in the pair to be give a common Overlap Group Identification.

I NEED FUNCTIONS WHICH CAN RETURN THE RESULTS WHICH ARE DISPLAYED IN THE EXAMPLE BELOW FOR THE "HAS OVERLAP" FIELD AND THE "OVERLAP GROUP ID" FIELD.

Below is an example of the sheet I'm working on. I have an example which I can attached (if allowed) so it's easier to understand than a big explanation. I've seen some similar posts regarding time overlaps, but none with this multi-criteria requirement. I successfully used the SUMPRODUCT function to identify one overlapping entry in a pair of overlapping entries, but I couldn't get it to identify both entries in the overlapping pair.

Hopefully someone can help me better understand how to accomplish this identification of overlapping times, and better understand the SUMPRODUCT function. If there is a better function to use, I'm all ears.

Thanks for the ideas.

EXAMPLE:
EmployeeCustomerTaskBillable TaskStart DateStart Date TimeEnd Date TimeHas OverlapOverlap Group IDExplanation
10Employee ACustomer 01Task 1TRUE3/26/20193/26/19 9:00 AM3/26/19 10:00 AMTRUEOverlap 01"The ""Has Overlap"" field is TRUE because this entry overlaps the entry on row 11 since the same employee has two entries which share the same 30 minute timeframe from 9:30 AM to 10:00 AM. Both overlapping entries have a Billable Task field value of TRUE, so they are recognized as overlaps.


The entry is given a Overlap Group ID of 1 because it is the first overlapping entry for any employee or customer."
11Employee ACustomer 02Task 1TRUE3/26/20193/26/19 9:30 AM3/26/19 11:00 AMTRUE
Overlap 01

<tbody>
</tbody>
"The ""Has Overlap"" field is TRUE because this entry overlaps the entry on row 10 since the same employee has two entries which share the same 30 minute timeframe from 9:30 AM to 10:00 AM. Both overlapping entries have a Billable Task field value of TRUE, so they are recognized as overlaps.


This entry is given an Overlap Group ID which matches that in row 10 because it is not the first of the overlapping entries which share this same timeframe for this employee or customer."
12Employee ACustomer 02Task 2FALSE3/26/20193/26/19 9:30 AM3/26/19 11:00 AMFALSEThough this entry overlaps two other entries in row 10 and 11, it DOES NOT have a "Billable Task" of TRUE and thus it does not represent an overlapping entry. So the "Has Overlap" field is FALSE.
13Employee ACustomer 04Task 1TRUE3/26/20193/26/19 11:00 AM3/26/19 12:00 PMFALSEThe "Has Overlap" field is FALSE because no other entry share this timeframe.
14Employee ACustomer 05Task 1TRUE3/26/20193/26/19 12:00 PM3/26/19 1:00 PMTRUEOverlap 02"The ""Has Overlap"" field is TRUE because this entry overlaps the entry on row 15 since the same customer has two entries which share the same 30 minute timeframe from 12:30 PM to 1:00 PM. Both overlapping entries have a Billable Task field value of TRUE, so they are recognized as overlaps.


The entry is given a Overlap Group ID of 2 because this is the second group of entries which share an overlapping timeframe."
15Employee BCustomer 05Task 1TRUE3/26/20193/26/19 12:30 PM3/26/19 1:30 PMTRUEOverlap 02"The ""Has Overlap"" field is TRUE because this entry overlaps the entry on row 14 since the same customer has two entries which share the same 30 minute timeframe from 12:30 PM to 1:00 PM. Both overlapping entries have a Billable Task field value of TRUE, so they are recognized as overlaps.


This entry is given an Overlap Group ID which matches that in row 14 because it is not the first of the overlapping entries which share this same timeframe for this employee or customer."
16Employee BCustomer 07Task 1TRUE3/26/20193/26/19 10:00 AM3/26/19 11:00 AMFALSEThe "Has Overlap" field is FALSE because No other entry with a "Billable Task" status share the same customer or same employee, and same timeframe.

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
For the first formula

<b>sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:114px;" /><col style="width:81px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >H</td><td >I</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >End Date Time</td><td >Has Overlap</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">26/03/2019 10:00</td><td >TRUE</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >I2</td><td >=IF(SUMPRODUCT((E2="TRUE")*($E$2:$E$8="TRUE")*(($G$2:$G$8<=G2)*($H$2:$H$8>G2)+($G$2:$G$8<H2)*($H$2:$H$8>=H2)>0)*(($B$2:$B$8=B2)+($C$2:$C$8=C2)>0))>1,"TRUE","FALSE")</td></tr></table></td></tr></table> <br /><br />
 

DMDGUSA

New Member
Joined
Mar 26, 2019
Messages
6
Thank you for the reply. Unfortunately when implementing your function with the example provided all rows in columns I (Has Overlap field) return FALSE. As seen in the example, many should be TRUE with only a few FALSE options. Can you confirm and possibly guide me where I'm going wrong.

Thanks.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
This is the first formula with column corrections


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Employee</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Customer</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Task</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Billable Task</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Start Date</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Start Date Time</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">End Date Time</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Has Overlap</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Overlap Group ID</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Employee A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Task 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TRUE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 09:00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 10:00</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TRUE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Employee A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 02</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Task 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TRUE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 09:30</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 11:00</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TRUE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Employee A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 02</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Task 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FALSE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 09:30</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 11:00</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FALSE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Employee A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 04</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Task 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TRUE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 11:00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 12:00</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FALSE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Employee A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 05</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Task 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TRUE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 12:00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 13:00</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TRUE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Employee B</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 05</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Task 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TRUE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 12:30</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 13:30</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TRUE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Employee B</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer 07</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Task 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TRUE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 10:00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26/03/2019 11:00</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FALSE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Hoja1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=IF(<font color="Blue">SUMPRODUCT(<font color="Red">(<font color="Green">ROW(<font color="Purple">D2</font>)<>ROW(<font color="Purple">$D$2:$D$8</font>)</font>)*(<font color="Green">D2="TRUE"</font>)*(<font color="Green">$D$2:$D$8="TRUE"</font>)*(<font color="Green">(<font color="Purple">$F$2:$F$8<(<font color="Teal">F2+0.0006</font>)</font>)*(<font color="Purple">$G$2:$G$8>F2</font>)+(<font color="Purple">$F$2:$F$8<G2</font>)*(<font color="Purple">$G$2:$G$8>(<font color="Teal">G2-0.0006</font>)</font>)>0</font>)*(<font color="Green">(<font color="Purple">$A$2:$A$8=A2</font>)+(<font color="Purple">$B$2:$B$8=B2</font>)>0</font>)</font>)>0,"TRUE","FALSE"</font>)</td></tr></tbody></table></td></tr></table><br />
 

DMDGUSA

New Member
Joined
Mar 26, 2019
Messages
6

ADVERTISEMENT

Thanks for working on this one with me Dante.

Your attached table did clarify the columns. But I'm still getting the same FALSE for each entry.
HTML:
=IF(SUMPRODUCT((ROW(D2)<>ROW($D$2:$D$8))*(D2="TRUE")*($D$2:$D$8="TRUE")*(($F$2:$F$8<(F2+0.0006))*($G$2:$G$8>F2)+($F$2:$F$8(G2-0.0006))>0)*(($A$2:$A$8=A2)+($B$2:$B$8=B2)>0))>0,"TRUE","FALSE")

Maybe you can help me understand what I'm doing wrong?

I understand that your functions defines the following criteria:
  1. The rows being compared must have different Billable Task value.
  2. The rows must all have a Billable Task value of TRUE. (This seems to contradict the bullet 1 above. Maybe I'm misunderstanding this portion of the function.)
  3. Time for all other rows must meet the following criteria:
    1. Start time for the relative row is after all available start times AND start time for the relative row begins before all other end times in the range.
    2. End time for the relative row is after all available start times AND end time for the relative row is before the end time of all other end times in the range.
    3. The Employee for the relative row is the same as all other employees in the range which meet the criteria.
    4. The Customer for the relative row is the same as all other Customers in the range which meet the criteria.
In your function, if the above criteria are met and the total count is greater than 1 (more than just the relative records being use for the criteria), then a value of TRUE is provided.

Do I understand the function correctly? Do you know why I am getting FALSE on all rows? Is it the logic between bullets 1 and 2 above.</g2)*($g$2:$g$8>
 

DMDGUSA

New Member
Joined
Mar 26, 2019
Messages
6
Man, it would really help to be able to attach the sheet I'm working on.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

What do you have in the cell?
"TRUE" or TRUE

The formula considers that you have the text "TRUE", but if you have the value TRUE, then change the formula to the following:

=IF(SUMPRODUCT((ROW(D2)<>ROW($D$2:$D$8))*(D2=TRUE)*($D$2:$D$8=TRUE)*(($F$2:$F$8<(F2+0.0006))*($G$2:$G$8>F2)+($F$2:$F$8<G2)*($G$2:$G$8>(G2-0.0006))>0)*(($A$2:$A$8=A2)+($B$2:$B$8=B2)>0))>0,TRUE,FALSE)
 

DMDGUSA

New Member
Joined
Mar 26, 2019
Messages
6
ha! How did I miss that? Thank you. Now it's working on my sheet too.

I'd love to understand this function combination a little more so I can add criteria. For example, if I was going to add a department, would I add it to the last set of criteria. I.e. "...(($A$2:$A$8=A2)+($B$2:$B$8=B2)+(NewCriteriaRange=NewCriteria)>0))>0..."? Am I correctly understanding the function?

Thanks again for all of the help.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Now, the explanation goes:
Complete formula

=IF(SUMPRODUCT(
(ROW(D2)<>ROW($D$2:$D$8))*
(D2="TRUE")*
($D$2:$D$8="TRUE")*
(($F$2:$F$8<(F2+0.0006))*($G$2:$G$8>F2)+($F$2:$F$8<G2)*($G$2:$G$8>(G2-0.0006))>0)*
(($A$2:$A$8=A2)+($B$2:$B$8=B2)>0)
)>0,"TRUE","FALSE"
)



= IF (SUMPRODUCT (
(ROW(D2) <> ROW ($D$2:$D$8))
Only check the rows different to D2, that is, it does not compare itself, since the result would be true.


* (D2=TRUE) *
Only check if "Billable Task" is TRUE, as in the case of row 4, it is FALSE, then it does not perform the comparison.


($D$2:$D$8=TRUE) *
Only compare with the other rows if "Billable Task" equals TRUE


(($F$2:$F$8 <(F2 + 0.0006)) * ($ G $ 2: $ G $ 8> F2)
If the range of startDate is less than my startDate and the range of endDate is greater than my startDate.
F2 + 0.00006 means 9:00 + one minute = 9:01, without this, if you compare a range of 8 to 9 against 9 to 10 it would be true, that's why I increase it by one minute


+ (this is important, it is to ask if the previous match is met or if the following condition is met)


($F$2:$F$8<g2) *="" ($g$2:$g$8=""> (G2-0.0006))> 0) *
Compare the final date with the date ranges


(
($A$2:$A$8 = A2)
If the employee matches
+
Or if the customer matches
($B$2:$B$8 = B2)> 0)


)> 0, TRUE, FALSE)
If all the conditions are met it will result in> 0 then TRUE, if any condition is not met, then the result is 0 (FALSE)


</g2)><g2) *="" ($g$2:$g$8="">I have problems to solve formula 2


</g2)>
 
Last edited:

DMDGUSA

New Member
Joined
Mar 26, 2019
Messages
6
Your explanation is very clear and the solution is eloquent. Thank you so much for taking the time to share. I feel like I can now use this with flexibility for other situations.

I can appreciate that the second portion, the Overlap Group ID is a tough one. Maybe someone else will have some clever ideas.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top