Quote Originally Posted by heresteve2 View Post
....The first 12 pay periods he accrues 55.38 hours and the last 14 he accrues 86.15. Added together that would be 141.53 which rounded should come to 142. Instead the formula is rounding each individually, 55.38 to 56 and 86.15 to 87 and then adding to 143. Is there a way to adjust that?
The rounding is done at the end - the discrepancy is because my formula is giving 11 pay periods at the lower amount, not 12 - perhaps the formula should refer to the last payperiod start date of the previous year, i.e. 28th December 2013, that will give the correct result

Quote Originally Posted by heresteve2 View Post
Also for people who have their first anniversary in the current year are not calculating correctly. The day after their 6 month anniversary they get 40 hours. And then every payroll after the accrue time base on an additional 40 hours. As an example row 4 should calculate out to 68. (40 after the 6 month anniversary and an additional 18 payrolls she accrues and additional 27.69 hours rounded to 28).
OK, I missed the part about paying 40 hours after 6 months. For a hire date of 7th October 2013, then, would there not be a 40 hour payment, then 13 payments at 40/26 and the last 5 at 80/26 (because they also hit the 1 year anniversary in October?) I make that a total of 75.

This revised formula would give those results

=ROUND(SUMPRODUCT(LOOKUP(DATEDIF(B3,DATE(2013,12,28)-14+ROW(INDIRECT("1:26"))*14,"m"),{0,6,12,60,120;0,40,80,120,160}))/26,0)+(YEAR(EDATE(B3,6))=2014)*40

The red parts would need to be changed for each year - 2014, obviously should be the year in question and the DATE part should be the last pay period start date of the previous year