Formula that counts days worked while excluding overlapping or partial overlapping days in Google Sheets

1
votes

I'm attempting to create a schedule that counts the number of days an employee has worked while ignoring the days that overlap. I have a spreadsheet with all employee activities that gets populated dynamically. Some of these activities overlap, some of them partially overlap. It seems like everything I tried today by searching through forums doesn't account for

a) multiple employees
b) dynamic formulas (x number of rows, x number of employees)
c) partial overlap

I attached a spreadsheet with the desired result in Column G https://docs.google.com/spreadsheets/d/1qjbjXFCYj1qXrVVGNnhOj11asxT_o1xHWXerRqAl1UQ/edit?usp=sharing

enter image description here

1
google-sheetstransposegoogle-sheets-formulaarray-formulasgoogle-sheets-query

1 Answers

1
votes
=ARRAYFORMULA(QUERY(UNIQUE(QUERY(SPLIT(TRANSPOSE(SPLIT(
 QUERY(TRANSPOSE(QUERY(TRANSPOSE("♠"&INDIRECT("A2:A"&COUNTA(B2:B)+1)&"♦"&
 SPLIT(REPT(INDIRECT("B2:B"&COUNTA(B2:B)+1)&"♣", 
 DAYS(INDIRECT("C2:C"&COUNTA(B2:B)+1), INDIRECT("B2:B"&COUNTA(B2:B)+1))+1), "♣")+
 TRANSPOSE(ROW(INDIRECT("A1:A"&MAX(DAYS(C2:C, B2:B)+1)))-1))
 ,,999^99)),,999^99), "♠")), "♦"), "where Col2 > 4000", 0)), 
 "select Col1,count(Col2) group by Col1 label count(Col2)''"))

0