Holiday entitlement

[macangc]

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

thanks in advance

 

[Zakkaroo]

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.

 

[macangc]

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

 

[mick0005]

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

=(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),))