Solved

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

Posted on 2012-09-18
358 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

LVL 39

Expert Comment

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

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

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

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

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

Try this update on buttersk's macro
Copy-of-PayCycle-1.xlsm
0

LVL 19

Assisted Solution

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

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

LVL 39

Expert Comment

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

LVL 19

Expert Comment

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

Author Closing Comment

Thanks guys, works perfectly!
0

## Featured Post

### Suggested Solutions

Advanced finding in Excel 9 26
Third Sunday of the Month 10 50
Excel 2010 question 3 26
Tricky lookup and concatenate formula 6 31
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

#### Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!