Holidays spreadsheet...Please help!

asked 2018-10-31 22:54:24 +0100

Specy88 gravatar image

Hi! I'm stuck! I'm trying to simplify my life but it doesn't look like I'm going in the right direction...that's why I need help!

image description

I'm trying to work out, with a apparently very complex formula, holidays that our employees accrued and use based on their hourly wage.

OK so let's take MARCH: C4 & C5 are employee's hours worked; D5 are holidays accrued (%12.07 of C3); E4 & E5 is the pay/h earned by the employee that month (E3 it's the average pay considering hours worked [=((C4E4)+(C5E5))/(C3)]); F3 it's simply hours of holidays taken by the employee.

Now, in March this employee didn't take any holiday so those 19.9hours accrued will go into April (paid £8.39/h). In April this employee took 25hours of holidays so I would need to pay 19.9hours at £8.39/h from March + 5.1hrs @ £9/h from April. And so on... Is there any way to this with formulas?

Please help!

David

edit retag flag offensive close merge delete