Pay Period Calculation

[schatham]

I need to have a formula to calculate the number of pay periods within a date range.

I have this code:

=SUM(IF((A1<=(IF(MOD(ROW(INDIRECT("1/1/2010"*1&":"&"12/31/2011"*1)),14)=13,ROW(INDIRECT("1/1/2010"*1&":"&"12/31/2011"*1)),0)))*(A2>=(IF(MOD(ROW(INDIRECT("1/1/2010"*1&":"&"12/31/2011"*1)),14)=13,ROW(INDIRECT("1/1/2010"*1&":"&"12/31/2011"*1))))),1,0))

The cells A1 & A2 have 03/07/2011 and 12/31/2011 respectively in them.

and it returns 22, which has been correct.

However, the definition of what a "pay period" is has been amended. In addition to it being a Friday (which is why the formula starts on 1/1/2010), it now must be at least a two week period also, which would make the new, correct answer 21.

Is there a way to modify the above formula to make that determination that there are 21 2 week periods between 3/7 & 12/31, and not 22?

Thanks!

 

[delaneyjm]

Cell B4, just enter:

=SUMPRODUCT(--((DATE(2011,1,8)-WEEKDAY(DATE(2011,1,2))+(ROW($A$1:$A$27)*14))>=A2),--((DATE(2011,1,8)-WEEKDAY(DATE(2011,1,2))+(ROW($A$1:$A$27)*14))<=A3))

 

[schatham]

Cell B4, just enter:

=SUMPRODUCT(--((DATE(2011,1,8)-WEEKDAY(DATE(2011,1,2))+(ROW($A$1:$A$27)*14))>=A2),--((DATE(2011,1,8)-WEEKDAY(DATE(2011,1,2))+(ROW($A$1:$A$27)*14))<=A3))

I must be missing something - it doesn't seem to work right all the time. From your formula, you have the 2nd Saturday (1/8/2011), the first Sunday (1/2/2011) and check it vs. the dates I had supplied (Start Date of 3/7/2011, Yr End Date 12/31/2011).

As I went thru March & April, it changed where it shouldn't have.

What I know is that there are 26 periods in a year, which is usually the 2nd & 4th Fridays of the month, and that if you don't come in at the beginning of a new period (i.e. one week into the current one), you bypass that one & pick up the next.

Example -

last period ended on 2/25/2011, new one begins on 2/28/2011.
First pay period in March 2011 = 3/11/2011.
Second pay period in March 2011 = 3/25/2011
The ones after that are 4/8 & 4/22.

If the start date was 3/7, then the 3/11 period would be bypassed since they were not on at the beginning of a period.

What am I missing?

 

[delaneyjm]

Slight modification to the formula.

=SUMPRODUCT(--((DATE(2011,1,4)-WEEKDAY(DATE(2011,1,2))+((ROW($A1:$A27)-1)*14))>=A2),--((DATE(2011,1,4)-WEEKDAY(DATE(2011,1,2))+((ROW($A1:$A27)-1)*14))<=A3))

The ((DATE(2011,1,4)-WEEKDAY(DATE(2011,1,2))+((ROW($A1:$A27)-1)*14)) portion determines the first Monday of the year.

If you want to see the dates it calculates, put this formula in a different range of cells and copy down.

=(DATE(2011,1,4)-WEEKDAY(DATE(2011,1,2))+((ROW($A1)-1)*14))