Calculate # of Paychecks based on Date of Hire

Please refer to Attached sheet for referance.

I have a business and i run payroll for my employees twice a month (semimonthly).
Date of paycheck will be 16th and 31st.
So if employee was hired on say 7/5/2009 then this employee will have 3 paychecks as of today
(1st paycheck from 7/1/2009 to 7/15/2009
2nd Paycheck from 7/16/2009 to 7/31/2009
3rd Paycheck from 8/1/2009 to 8/15/2009)

i need to know the # of pay checks for each employee computed in Cell C3 to C7.


Let me know if you have any questions.

Thank you for your help..

Riz Momin

Using your posted workbook...try this:

C3: =SUMPRODUCT(--(DAY(ROW(INDEX(A:A,$B3):INDEX(A:A,$B$1)))={16,31}))

Copy that formula down through C7

With your posted data the formulas return these values:

C3:  3
C4: 27
C5: 26
C6: 26
C7: 10

One issue to be resolved...If a person's hire date is ON a payday, I assume they won't receive a paycheck on that date. If that's true, use this formula:

C3: =SUMPRODUCT(--(DAY(ROW(INDEX(A:A,$B3+1):INDEX(A:A,$B$1)))={16,31}))

The new values returned will be these:

C3:  3
C4: 27
C5: 26
C6: 25
C7: 10

Is that something you can work with?

Since the mid-month pay date is one day in arrears, and the end-of-month pay is current, I'd think you would not get a m-m check if you started on the 16th, but would get an e-o-m check if you started on the 31st.

You wouldn't get paid if you started on the 16th because the pay period ended on the 15th ...