CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulafilteringspreadsheet
user19438699
user19438699

Reputation:

Filtering Overlapping Dates within Set Date Range

I have a couple of questions I need for a semi-annual report:

I have a list of interns, each with their own start and end dates of service. I am looking to filter the list of those who worked within a given range of dates. I want any intern who has their term of service that overlaps with the range dates.

I am also looking to calculate how many weeks + months they worked within the same range of dates.

Here is the Spreadsheet.

I have tried your basic filter with date ranges, but I am not able to figure out the overlapping aspect of the list. For example, if the date range is 2021-2022, and an intern worked from 2020-2022, it would not filter it, as the start date is outside of the range.

Any help would be greatly appreciated!

Upvotes: 1

Views: 511

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

Check for Overlap

For a simple pull-down formula to check if each intern has any time overlapping the range, use the standard Overlap Formula:

Min(end1,end2)-Max(start1,start2)

which gives

=if(min(C2,G$2)>=max(B2,F$2),"Yes","No")

or for an array formula:

=ArrayFormula(lambda(start,end,if(if(end<G2,end,G2)>=if(start>F2,start,F2),"Yes","No"))
(filter(B2:B,A2:A<>""),filter(C2:C,A2:A<>"")))

Months

@Markus's approach is perfect, but for an alternative one could use:

=datedif(eomonth(max(B2,F$2),-1)+1,eomonth(min(C2,$G$2),0)+1,"M")

(for Jeremy as an example) in other words dial the start date back to the first of the month and the finish date forwards to the start of the next month and use Datedif to get the overlap in full months. The point of this is that it could be adapted for weeks as well.

With the lookup on names in column I2:

=datedif(eomonth(max(xlookup(I2,A$2:A$8,B$2:B$8),F$2),-1)+1,eomonth(min(xlookup(I2,A$2:A$8,C$2:C$8),$G$2),0)+1,"M")

or as an array formula:

=ArrayFormula(lambda(rNames,Names,start,end,datedif(eomonth(if(xlookup(rnames,Names,start)>F2,xlookup(rnames,Names,start),F2),-1)+1,eomonth(if(xlookup(rnames,Names,end)<G2,xlookup(rnames,Names,end),G2),0)+1,"M"))
(filter(I2:I,I2:I<>""),filter(A2:A,A2:A<>""),filter(B2:B,A2:A<>""),filter(C2:C,A2:A<>"")))

Weeks

You have a few options here.

(1) Having found the number of complete or partial months of overlap, just divide the number of days in those months by seven to get corresponding number of weeks:

=ArrayFormula(lambda(rNames,Names,start,end,quotient(datedif(eomonth(if(xlookup(rnames,Names,start)>F2,xlookup(rnames,Names,start),F2),-1)+1,eomonth(if(xlookup(rnames,Names,end)<G2,xlookup(rnames,Names,end),G2),0)+1,"D"),7))
(filter(I2:I,I2:I<>""),filter(A2:A,A2:A<>""),filter(B2:B,A2:A<>""),filter(C2:C,A2:A<>"")))

(2) Use the same logic as for months to get number of partial or complete working weeks of overlap:

=ArrayFormula(lambda(rNames,Names,start,end,quotient(datedif(if(xlookup(rnames,Names,start)>F2,xlookup(rnames,Names,start),F2)-weekday(if(xlookup(rnames,Names,start)>F2,xlookup(rnames,Names,start),F2)),if(xlookup(rnames,Names,end)<G2,xlookup(rnames,Names,end),G2)+7-weekday(if(xlookup(rnames,Names,end)<G2,xlookup(rnames,Names,end),G2),3),"D"),7))
(filter(I2:I,I2:I<>""),filter(A2:A,A2:A<>""),filter(B2:B,A2:A<>""),filter(C2:C,A2:A<>"")))

(3) Just get number of complete weeks of overlap by dividing number of days by 7:

=ArrayFormula(lambda(rNames,Names,start,end,quotient(datedif(if(xlookup(rnames,Names,start)>F2,xlookup(rnames,Names,start),F2),if(xlookup(rnames,Names,end)<G2,xlookup(rnames,Names,end),G2),"D"),7))
(filter(I2:I,I2:I<>""),filter(A2:A,A2:A<>""),filter(B2:B,A2:A<>""),filter(C2:C,A2:A<>"")))

enter image description here

Upvotes: 0

muw
muw

Reputation: 385

This formula will work for all 3 columns:

=FILTER(
    {
    A2:A,
    ARRAYFORMULA(
        1+
        TRUNC(ARRAYFORMULA(IF(C2:C<G2,C2:C,G2)-43101)/7)-
        TRUNC(ARRAYFORMULA(IF(B2:B>F2,B2:B,F2)-43101)/7)
    ),
    ARRAYFORMULA(
        1+
        DATEDIF(
            ARRAYFORMULA(IF(B2:B>F2,B2:B,F2)),
            ARRAYFORMULA(IF(C2:C<G2,C2:C,G2)),
            "M"
        )
       )
    },
    ARRAYFORMULA((IF(C2:C>G2,G2,C2:C) - IF(B2:B<F2,F2,B2:B)) >0 )
)

For calculating the months the formula uses the DATEDIF function.

For calculating the weeks, I am choosing as reference an arbitrarily defined week zero starting on Monday, 2018-01-01 (day 43101 in the Network Time Protocol).

Link to Google Sheet

Upvotes: 1

Related Questions