I am trying to find a formula to give me my accruals earned month to date. The PTO allotted varies as follows

1 - 3 = 13 days = 8.66 per month

120 = 10 days =10 hours per month

4-8 - 144 hrs = 18 days = 12 hrs per month

9 + =184 hrs 23 days = 15.33 hours per month

sometimes employees negotiate and are given 120 hours from DOH as seen above

I am trying to enter a formula that will give me the total hours earned so far from date of hire to current date. They are given the hours accrued the first day of the following month after I process payroll.


my attached sheet shows

C2 - C5 - PTO given

D2 - D5 - DOH

E2 - E5 - formula for todays date

F2 - F5 - formula for years of service

G2 - G5 - formula to calc hrs per month earned from cell "C2 and so on "

H2 - Months employed

I2 - trying to get formula to give me hours accrued so far from DOH ( should come out to 51.96 )

side note PTO is pro rated from DOH my pay periods are semi monthly ( 1st through the 15th ) then the (16th through the end of month ) so DOH in cell C2 new hire would be a normal 104 yrly allotted PTO given at a pro rate amount would be - 86.68 ( I front load ) I take the 104 / 12 = 8.66 so it was 104 - 17.33 ( for Jan & Feb since they were not hired till 03/04/2013 )

it won't let me attache my excel file I am so sorry...... any assistance on this would be greatly appreciated pleeeeeeeeeeeeeeeease