Forum Discussion

Cara Hunter's avatar
Cara Hunter
Copper Contributor
Jul 12, 2018
Solved

Overlap Formla by Criteria

Attached is the excel sheet.  I just need to know what times overlap.  Should be easy, however, I can have the same person on the same date.  How do I tell excel to look at overlaps only when the names and dates match and only to look in that range?  So if the name and dates are the same, check for overlaps.  But only check for overlaps in that particular range.  Gosh I hope that makes sense.  I left formulas in the excel that I have been trying.

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 15, 2018

    Hi Cara,

     

    Dates and times in file attached were presented as text, reformat to proper format. Formula for overlaps could be like

    =IF(SUMPRODUCT((B2=$B$2:$B$7)*($J2>$I$2:$I$7)*(ROW(J2)<>(ROW($J$2:$J$7)))*(I2<$J$2:$J$7)*(D2=$D$2:$D$7)),"Yes","No")

    and attached

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 15, 2018

    Hi Cara,

     

    Dates and times in file attached were presented as text, reformat to proper format. Formula for overlaps could be like

    =IF(SUMPRODUCT((B2=$B$2:$B$7)*($J2>$I$2:$I$7)*(ROW(J2)<>(ROW($J$2:$J$7)))*(I2<$J$2:$J$7)*(D2=$D$2:$D$7)),"Yes","No")

    and attached

Resources