Days worked in Month - PQ/M functions possibly

[davidmaddock54]

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, although I am also playing around with using Power Query (so trying understand M function syntax) as it allows for some data control and cleaning I like.

I'll add a dummy spreadsheet soon enough, but basically users will input a client name, and then a date when work commenced, and when work ceased with said client. Doing a simple days between calculation is easy. But this is a per month report. And the commence and cease dates could go before or after pertinent month. 

This is a manual example of the results I want. 

Because of the different start and end points, there's always a hickup in the formulas I've come up with, particularly as they all need to know the days in the Month. (The answer may be to do a workbook with 12 months set across tabs.)

Any ideas? Have I been clear in explanation?

[#2]

Hi @davidmaddock54 

Below formula will give you the desired results:

=IF((AND(B2<(DATE(YEAR($G$1),MONTH($G$1),1)),C2>EOMONTH($G$1,0))),(EOMONTH($G$1,0)-(DATE(YEAR($G$1),MONTH($G$1),1))+1),IF(AND(B2<(DATE(YEAR($G$1),MONTH($G$1),1)),C2<=EOMONTH($G$1,0)),(C2-(DATE(YEAR($G$1),MONTH($G$1),1))+1),IF(AND(B2>=(DATE(YEAR($G$1),MONTH($G$1),1)),C2>=EOMONTH($G$1,0)),(EOMONTH($G$1,0)-B2+0),(C2-B2+0))))

A sample file is also attached for your reference. Hope it will help.

Thanks

Tauqeer

[#3 · Accepted solution]

@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

[#4]

Hi @Riny_van_Eekelen 

Ideally, your variant should be used as it is more simple and straightforward.

Nice one.

Thanks

Tauqeer

[#5]

That seems like some sort of magic to be that short. Will be testing in actual sheet shortly. Thank you so much.

[#6]

Thank you. Certainly, my formulas were reaching that sort of length, but they weren't working!

[#7]

And it works a treat. Added an IF formula as I needed cells to stay blank if data was incomplete and we're set. Thank you all.

[#8]

@davidmaddock54 It isn't all that magical. Dates in Excel are in fact sequential numbers, starting at 1 for January 1, 1900.

So, with your problem the end date should not be after July 31 (i.e. minimum value of July 31 and the end date) and the start date should not be before July 1 (i.e. maximum value of July 1 and the start date). You can use the MIN and MAX functions for that. The EDATE function comes in to "calculate" the first day of the month, based on the given end date, but you could aslo enter the start date into its own cell and refer directly to that one.

[#9]

@davidmaddock54 

The rules governing your use of the data may prevent this, but it is technically possible for the work period not to overlap the pertinent month, returning a negative duration.  In such cases a further MAX would be called for, giving

= MAX( 
    1 + MIN([@Ceased],    monthEnding) 
      - MAX([@Commenced], 1+EOMONTH(monthEnding,-1)),
    0
  )

[#10]

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