Mar 14 2022 03:50 AM
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.
Mar 14 2022 10:17 AM
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.
Mar 16 2022 03:03 AM
@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.
Mar 16 2022 03:55 AM