Find out if contracts are consecutive for the last 6 months

[Guadart]

Hello,

I've got several employees identified by ID for which I can have several contracts with a start date in one column and an end date in the following one and I need to find out for each employee if the contracts sum up for at least the last 6 months without interruption.

I thought about finding out if start of contract n+1 and end of contract n is different of 1 and then sum those dates to see if they last 6 months but I can't translate it nicely in a formula.  Any help would be greatly appreciated, thanks in advance.

[#2]

@Guadart 

Hi, I only develop for Excel 365 / 2021 so the details of my solution may not be relevant.

= MAP(UNIQUE(ID), DaysOutOfContractλ)

where "DaysOutOfContractλ" is given by
= LAMBDA(distinctID,
     LET(
         startByID,       FILTER(Start, ID=distinctID),
         endByID,         FILTER(End, ID=distinctID),
         totalDuration,   1 + MAX(endByID)-MIN(startByID),
         totalContracted, SUM(1+endByID-startByID),
         outOfContract,   totalDuration-totalContracted,
         outOfContract
     )
  )

The key element that should be transferable to legacy Excel is to count the days under contract rather than those out of contract.

[#3]

@Peter BartholomewThank you very much for your help ! I don't know VBA much so I need to check your solution with a coworker. I'll come back to say how it worked out. Thanks again.

[#4]

First and foremost bear in mind that this solution is only usable if you have access to the latest versions of Excel 365. I can fully see how the misunderstanding arises, but the formula is a regular worksheet formula, albeit using functions that have only recently become available in Excel 365. No doubt some of the ideas would be transferable to VBA with a bit of refactoring.

What the LET function does is assign names to fragments of a nested formula which allows the formula to be written as if it were multiple statements. LAMBDA is similar, except that values are read from a parameter string, turning any formula into a function. Whether these changes to Excel fill you with horror and dread or excitement is a very individual matter.