Time Overlap

[tamtammg]

Hi All,

I've tried and tried but couldn't figure this one out...

Attached is a sample data set for employees' Worked hours and Meal breaks at locations on a given date.

I need to figure out if the meal breaks are covered by other working employees. And indicate if the breaks are fully covered (entire time frame), partially covered, or have no coverage (Col G).

I found another post which mentioned Sumproduct would work, but it's not giving me what I needed.

=SUMPRODUCT(--([Location Worked]=[@[Location Worked]]),--([Transaction Date]=[@[Transaction Date]]),([@[Transaction Start Date/Time]]<=[Transaction Start Date/Time])*([@[Transaction End Date/Time]]>=[Transaction End Date/Time]))

Please help!!!

Break	Employee Name	Location Worked	Transaction Date	Transaction Start Date/Time	Transaction End Date/Time	Desired Result
Meal Break	Employee F	Location B	6/20/2020	6/20/20 2:03 PM	6/20/20 3:01 PM	Partial Coverage
Worked Hours	Employee F	Location B	6/20/2020	6/20/20 3:00 PM	6/20/20 7:00 PM
Worked Hours	Employee H	Location B	6/20/2020	6/20/20 8:50 AM	6/20/20 2:30 PM
Worked Hours	Employee E	Location C	6/20/2020	6/20/20 9:00 AM	6/20/20 12:00 PM
Worked Hours	Employee E	Location C	6/20/2020	6/20/20 1:00 PM	6/20/20 5:58 PM
Meal Break	Employee E	Location C	6/20/2020	6/20/20 12:00 PM	6/20/20 1:00 PM	Full Coverage
Worked Hours	Employee G	Location C	6/20/2020	6/20/20 3:46 PM	6/20/20 8:56 PM
Worked Hours	Employee G	Location C	6/20/2020	6/20/20 11:17 AM	6/20/20 2:46 PM
Meal Break	Employee G	Location C	6/21/2020	6/21/20 3:05 PM	6/21/20 3:26 PM	No Coverage
Worked Hours	Employee G	Location C	6/21/2020	6/21/20 3:26 PM	6/21/20 6:13 PM
Worked Hours	Employee G	Location C	6/21/2020	6/21/20 11:04 AM	6/21/20 3:05 PM
Meal Break	Employee B	Location D	6/22/2020	6/22/20 1:48 PM	6/22/20 2:55 PM	Full Coverage
Worked Hours	Employee B	Location D	6/22/2020	6/22/20 9:31 AM	6/22/20 1:48 PM
Worked Hours	Employee B	Location D	6/22/2020	6/22/20 2:55 PM	6/22/20 6:11 PM
Worked Hours	Employee H	Location D	6/22/2020	6/22/20 2:04 PM	6/22/20 5:08 PM
Worked Hours	Employee H	Location D	6/22/2020	6/22/20 8:38 AM	6/22/20 1:04 PM
Worked Hours	Employee I	Location D	6/22/2020	6/22/20 10:05 AM	6/22/20 11:57 AM
Worked Hours	Employee I	Location D	6/22/2020	6/22/20 12:56 PM	6/22/20 6:33 PM
Worked Hours	Employee L	Location D	6/22/2020	6/22/20 12:30 PM	6/22/20 7:30 PM
Worked Hours	Employee L	Location D	6/22/2020	6/22/20 10:00 AM	6/22/20 12:00 PM
Worked Hours	Employee N	Location D	6/22/2020	6/22/20 12:35 PM	6/22/20 4:56 PM
Worked Hours	Employee N	Location D	6/22/2020	6/22/20 8:55 AM	6/22/20 12:05 PM
Worked Hours	Employee O	Location D	6/22/2020	6/22/20 5:53 PM	6/22/20 9:03 PM
Worked Hours	Employee O	Location D	6/22/2020	6/22/20 1:02 PM	6/22/20 5:00 PM
Worked Hours	Employee P	Location D	6/22/2020	6/22/20 4:33 PM	6/22/20 9:04 PM
Worked Hours	Employee P	Location D	6/22/2020	6/22/20 11:13 AM	6/22/20 3:30 PM

 

[bstory84]

This is not very elegant, but it works for me.

=IF([@Break]="Meal Break",IF(SUMIFS([Transaction Start Date/Time],[Break],"Worked Hours",[Location Worked],[@[Location Worked]],[Transaction Start Date/Time],"<"&[@[Transaction Start Date/Time]],[Transaction End Date/Time],">"&[@[Transaction End Date/Time]])>0,"Full Coverage",IF(SUMIFS([Transaction Start Date/Time],[Break],"Worked Hours",[Location Worked],[@[Location Worked]],[Transaction Start Date/Time],"<"&[@[Transaction End Date/Time]],[Transaction End Date/Time],">"&[@[Transaction End Date/Time]])>0,"Partial Coverage","No Coverage")),"")

 

[tamtammg]

This is not very elegant, but it works for me.

=IF([@Break]="Meal Break",IF(SUMIFS([Transaction Start Date/Time],[Break],"Worked Hours",[Location Worked],[@[Location Worked]],[Transaction Start Date/Time],"<"&[@[Transaction Start Date/Time]],[Transaction End Date/Time],">"&[@[Transaction End Date/Time]])>0,"Full Coverage",IF(SUMIFS([Transaction Start Date/Time],[Break],"Worked Hours",[Location Worked],[@[Location Worked]],[Transaction Start Date/Time],"<"&[@[Transaction End Date/Time]],[Transaction End Date/Time],">"&[@[Transaction End Date/Time]])>0,"Partial Coverage","No Coverage")),"")

You're a genius! This works! Thank you!!!