# Pay cycle vacation accrual

#### tripper

##### New Member
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?

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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)

Replies
17
Views
1K
Replies
1
Views
984
Replies
2
Views
559
Replies
2
Views
318
Replies
0
Views
410

1,207,260
Messages
6,077,351
Members
446,279
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back