Forum Discussion
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,...
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
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
)