[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
11
Medium Priority
?
387 Views
Last Modified: 2012-09-19
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
Comment
Question by:dplowman
  • 4
  • 4
  • 3
11 Comments
 
LVL 39

Expert Comment

by:nutsch
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

by:dplowman
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

by:nutsch
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 19

Expert Comment

by:Ken Butters
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.


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
0
 

Author Comment

by:dplowman
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

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

Assisted Solution

by:Ken Butters
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

by:Ken Butters
ID: 38411005
sorry nutchs... I should have refreshed page prior to posting.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38411006
I'm sorry for you, you redid the work.
0
 
LVL 19

Expert Comment

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

Author Closing Comment

by:dplowman
ID: 38411174
Thanks guys, works perfectly!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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.

613 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question