Number of Pay Periods in Range with SumProduct

Hi Folks,

I am trying to calculate the number of pay periods falling in between two dates, based off a table of pay period dates and coverage start / end dates. Sample spreadsheet attached.

=SUMPRODUCT(--(Payroll!$D$2:$D$27>='List of Enrolled EE''s'!B2)*(--(Payroll!$E$2:$E$27<='List of Enrolled EE''s'!C2)))

This is my working formula. It's correct some of the time but not all. I can't just add +1 to it, as then my correct numbers are changed too.

Sample Spreadsheet.xlsx

Thoughts? Thanks so much for your help!

I find no dates in Payroll!$E$2:$E$27 <= 1/2/2015. So the zero in D28 of ‘List of Enrolled EE's’ is correct. I haven’t checked the others.

Edit I checked the rest of the cells marked 'should be' and they are correct, too.

For ex. Morticia Addams with coverage from 1/1 - 1/2, there should be one pay period within which those dates fall (the pay period running 12/31/14 - 1/3/15. So it should return 1, not 0.

Please disregard I found the answers to these.


Should holidays and weekends be considered? 1/1/15 is a holiday and 1/3/15 is a Saturday.

Also in the above you indicate pay period running 12/31/14 - 1/3/15. I don't know if it will matter or not but there is

a pay period 12/21/14 - 1/3/15 but not 12/31/14 - 1/3/15.

I assume one of these is a typo. Which one? If the period is supposed to be from 12/21 there is another holiday if it matters.

I still cannot get all of the counts to agree with your expected numbers. I keep coming up with new disagreements, though. LOL

Try this in D2 filled down.

Formula:

Please Login or Register  to view this content.

Edit You almost had it. The references just needed to be crossed.

Also this works and does not require arrays.

Formula:

Please Login or Register  to view this content.

You are welcome. Thank you for the feedback.