# Holiday entitlement

#### macangc

##### Board Regular
Hi I have a spreadsheet for showing holidays and want to work out what we are entitled to based on our start date

Formula goes in A1 and we have 25 days holiday a year (exc BH)

Start date is in E1 and our holidays run Jan to dec

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Are you saying that you want to calculate the entitlement of holiday for people who start working for the company, after the holiday period begins? e.g. someone starts in February 2015, how many holidays will they have available until December 2015?

With the exception of new-starters who begin after January, surely everyone in the company would just have 25 days?

If that is what you're after, wouldn't it depend on your companies policy for holiday accrument? Different companies may have different policies for how many holiday days are accrued.

Last edited:
Yes thats correct. I work as part of a start up so we are employing people as we go. The 25 days is the annual entitlement so i worked that out as 0.068 per day over the 365 in the year.

Its the formula to work out that figure for someone who started on the 2nd March for example that I am struggling with. I have done it but i'm using a number of fields to create it and hoped there may be a better way i.e. a single formula along with their start date

thanks

Yes thats correct. I work as part of a start up so we are employing people as we go. The 25 days is the annual entitlement so i worked that out as 0.068 per day over the 365 in the year.

Its the formula to work out that figure for someone who started on the 2nd March for example that I am struggling with. I have done it but i'm using a number of fields to create it and hoped there may be a better way i.e. a single formula along with their start date

thanks

I think you may want to calculate the accrual based on net work days instead of all days in the year (excludes weekends). You'd need to put in your own public holidays into the networkdays function because I don't know your region.

This basically determines how many work days there are in the current year, subtracts the number of work days that have passed prior to hire from that, and then divides that difference into 25.

FYI, a good way to write a complex formula that you normally can't figure out how to combine into a single one is to write it in several pieces... put each component of it into a separate cell, and then eventually write a formula that just refers to the cells themselves to combine. Finally, you can replace the references to individual cells with the actual formula that resides in that cell. Thats how I built the one below.

Assumes the hire date is in cell A2

Code:
``=(NETWORKDAYS(DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),12,31),)-NETWORKDAYS(DATE(YEAR(TODAY()),1,1),A2))*(25/NETWORKDAYS(DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),12,31),))``

Last edited:

Replies
2
Views
417
Replies
2
Views
205
Replies
3
Views
440
Replies
5
Views
240
Replies
4
Views
158

1,196,252
Messages
6,014,265
Members
441,809
Latest member
pawansher2002

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