Pay cycle vacation accrual

tripper

New Member
Joined
Jun 7, 2012
Messages
2
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?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top