Calculate # of Paychecks based on Date of Hire

[rizmomin]

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

[Ron Coderre]

Using your posted workbook...try this:

Please Login or Register  to view this content.

Copy that formula down through C7

With your posted data the formulas return these values:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

The new values returned will be these:

Please Login or Register  to view this content.

Is that something you can work with?

[rizmomin]

This works great...
i am really learning a lot from this site and shows how powerful MS Excel can get...

Thanks a lot

Riz Momin

[shg]

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.

[daddylonglegs]

Date of paycheck will be 16th and 31st.

What happens in those months that don't have a 31st?

Assuming that payday will be on the last day of the month in that scenario...

....then you can use either of these formulas to calculate the number of paychecks between hire date in B3 and todays' date in B1, assuming that you do get paid on the day you start if that happens to be a payday......

=DATEDIF(B3-DAY(B3)+1,B$1+1,"m")*2+(DAY(B3)<=16)-(DAY(B$1+1)<16)

or

=SUMPRODUCT(--(DAY(B3+ROW(INDIRECT("1:"&B$1-B3+1)))={17,1}))

[shg]

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

[daddylonglegs]

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

I see what you mean, shg. In which case you can use the 1st formula I suggested but with the = sign removed from the middle, i.e.

=DATEDIF(B3-DAY(B3)+1,B$1+1,"m")*2+(DAY(B3)<16)-(DAY(B$1+1)<16)