Find out if contracts are consecutive for the last 6 months

New Contributor

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.

Capture d’écran 2022-03-14 110405.png

3 Replies

@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.

@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.

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.