asked on

Excel: How to calculate number of paychecks YTD based on date and pay cyce

I am trying to create a formula to take a most recent paydate and paycycle to calculate how many paychecks have been received YTD.

For example. Paydate 9/14/2012, Paycycle Weekly would result in 36 paychecks.

nutsch

Try this formula with 7 as the weekly cycle:

=INT((DATEVALUE("9/14/2012")-DATE(YEAR(DATEVALUE("9/14/2012")),1,1))/7)

Thomas

dplowman

ASKER

Thanks for the reply. This formula gives me 36 paychecks like I mentioned above, but it should really be 37 right?

Also, I would need to calculate this based on pay frequency as well, bi-weekly, monthly, semi-monthly, and bi-monthly.

nutsch

Assuming your last paydate is in A1

Weekly
=1+INT((A1-DATE(YEAR(A1),1,1))/7)
Bi-weekly
=1+INT((A1-DATE(YEAR(A1),1,1))/14)
Monthly
=month(a1)
semi-monthly
=month(a1)*2-if(day(a1)<16,1,0)
bi-monthly
=ISODD(MONTH(A1))+INT(MONTH(A1)/2)

Depending on how you want to display it, you can build an intricate if structure to cover all bases.

Thomas

Ken Butters

I used VBA to create a User Defined function.

if you click on the paycycle cell... it will give you a dropdown to change the cycle.

NOTE: I forgot to Hit Save before uploading... so if you already downloaded before you read this... you will need to re-download the updated copy.
PayCycle.xlsm

dplowman

ASKER

Thanks buttersk. It looks like the weekly and biweekly cells are working. Howver, monthly is showing 12, twice a month 24, and quarterly 4.

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Ken Butters

membership