Forum Discussion

Brass Contributor

Dec 16, 2021

Solved

Days worked in Month - PQ/M functions possibly

Hi Folks, I am trying to automate a spreadsheet that seems like it should be easy, but there's a hurdle that's tricking me. I don't mind if I have a solution that uses regular excel formulas,...

BI & Data Analysis

excel

Formulas and Functions

Riny_van_Eekelen
Dec 16, 2021
davidmaddock54 As a variant, using the example kindly provided by tauqeeracma , try this:
=MIN($G$1,C2)-MAX(EDATE($G$1,-1)+1,B2)+1

PeterBartholomew1

Silver Contributor

Dec 17, 2021

davidmaddock54

Sometimes 365 simplifies calculations. This one appears to remain somewhat heavy-going.

= BYROW(interval,
     LAMBDA(anInterval,
        IF(OR(anInterval=0), "",
           LET(
             commenced,      INDEX(anInterval,1),
             ceased,         INDEX(anInterval,2),
             monthBeginning, EOMONTH(monthEnding,-1) + 1,
             worked,    1 +  MIN(ceased,monthEnding) - MAX(commenced, monthBeginning),
             IF(worked>0, worked, "")
           )
        )
     )
  )

The saving grace is that the calculation can be hidden within a Named Lambda function, giving

= BYROW(interval, DaysWorkedλ)

which is easier on the eye.