# Holiday calculator need help - time sensitive

#### colinheslop1984

##### Board Regular
I'm creating a holiday tracker that has certain requirements and I'm stuck. I feel like the solution is something simple but I'm giving up and asking for help.

I'll do my best to explain...

Holidays are accrued at the rates you see in right most side of the table based on length of service.

Holiday is calculated as follows:

Accrual rate x contracted hours = annual hours

If starting part way through year then calculated as:

Annual hours / 52 x weeks left in year

So Joe for example:

would accrue 5.6 x 40 =224 hours , 224 / 52 x 48 = allowance

As length of service increases, so does Accrual as seen on the right of the table below.

For example Peter
Would accrue 36 week at rate of 6, but 16 weeks at 5.8
So 6x30 / 52 x 36 + 5.8x30 / 52 x 16

I need a formula to calculate this in the annual allowance column, the ones I've tried are not accurately calculating

Then I also want to calculate accrued to date, using current week in top left corner as a criteria

Can any one put me out of my misery and help

 Current week -> 37 Contracted hours start week length of service annual allowance accrued to date Accrual rate length of service Joe 40 4 0 5.6 0 Peter 30 16 2 5.8 1 6 2 6.2 3 6.4 4

<tbody>
</tbody>

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### RasGhul

##### Well-known Member
Hi Colin,

Is this what you need? Note that the accrued to date sumifs adds annual allowance for the staff name, this name should be the full name to keep the sum criteria unique;

Replies
1
Views
301
Replies
0
Views
329
Replies
1
Views
454
Replies
1
Views
278
Replies
1
Views
802

1,127,100
Messages
5,622,725
Members
415,924
Latest member
togo

### 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