Pay cycle vacation accrual

[tripper]

Hoping someone will be able to help me out with this accrual issue I'm having. I have looked at other threads and tried to make them work for what I need but keep getting errors.

The issue I'm having is trying to calculate the accrual for vacation in hours for each pay cycle and having issues calculating the number of pay cycles their vacation has been accrued on so far.

Here's the details of my sheet:
A1: Name
B: blank column colored to block into sections
C1: pay band acronym (just for information sake)
D1: Accrual rate/pay cycle (this is dependent on C1)
E1: Start date (start of fiscal year or hire date whichever is later)
F1: end date - have current formula set to =TODAY()
G: blank column colored to block into sections
H1: carryover hours from prevoius year (just a number)
I1: # of pay cycles the accrual is calculated on
J1: Amount of accrued time in hours
K1: total earned (sum of H+J)

Our pay is semi-monthly, so vacation will be accrued in the same way - semi-monthly. On the 15th and 30th of each month. If an employee starts on or before the 15th, they will accrue for that day. If they begin after the 15th, they would only accrue for the 30th. We have broken the accrual rate down to be by pay cycle instead of a monthly amount.

The issue I am having is columns I and J work correctly.

Anyone have thoughts?

 

[barry houdini]

So the I1 formula needs to count the number of pay days between the dates in E1 and F1? I assume that in February the second payday will be on the last day of the month, in which case try this formula for I1

=SUMPRODUCT((DAY(ROW(INDIRECT(E1&":"&F1)))={15,30})+0)+SUMPRODUCT((TEXT(ROW(INDIRECT(E1&":"&F1))+1,"dmmm")="1mar")+0)