[Webinar] Streamline your web hosting managementRegister Today

x
Solved

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

Posted on 2012-09-18
Medium Priority
387 Views
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.
0
Question by:dplowman
• 4
• 4
• 3

LVL 39

Expert Comment

ID: 38410654
Try this formula with 7 as the weekly cycle:

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

Thomas
0

Author Comment

ID: 38410668
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.
0

LVL 39

Expert Comment

ID: 38410765
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
0

LVL 19

Expert Comment

ID: 38410772
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.

PayCycle.xlsm
0

Author Comment

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

LVL 39

Accepted Solution

nutsch earned 1000 total points
ID: 38410904
Try this update on buttersk's macro
Copy-of-PayCycle-1.xlsm
0

LVL 19

Assisted Solution

Ken Butters earned 1000 total points
ID: 38411000
yep... at first I thought you had wanted count of paydates per year... and forgot to update the ones that would have been calculated as constants.

Attached updated file.
PayCycle.xlsm
0

LVL 19

Expert Comment

ID: 38411005
sorry nutchs... I should have refreshed page prior to posting.
0

LVL 39

Expert Comment

ID: 38411006
I'm sorry for you, you redid the work.
0

LVL 19

Expert Comment

ID: 38411021
actually updated code by nutch is slicker... but just after just a deskcheck... looks like
both should work.
0

Author Closing Comment

ID: 38411174
Thanks guys, works perfectly!
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Suggested Courses
Course of the Month8 days, 10 hours left to enroll