Holiday Entitlement Formula

[Calamity98]

Hiya

I would be grateful for any help on this – it is probably really simple and I am trying to over complicate things but I cannot get it right.

I would like a really simple formula to work out our staff holidays on a rolling day to day basis for example:-

Employee
John Smith

First Day
20 March 2014

Last Day
3 August 2014

Annual Holiday Entitlement
20 days

(I do not include bank holidays in this calculation as we sort them out separately).

Is there a formula to work out how many days holiday John is entitled to when he leaves the company?

Many thanks

Kathy

[Russell Dawson]

How many days in the year does John have to work to get the full 20 day entitlement?

[bart hoekstra]

try to use:
formula inJust the table(1).xlsx

[Calamity98]

Hiya Russell

I would work out the annual leave from say 20 March 2014 - 19 March 2015. I have not worked out the exact amount of days - maybe this is where I am going wrong!

[Calamity98]

Thank for replying Bart.

I am not sure if I am missing a file, however I can't find the calendar file you said you uploaded first.

I am a bit confused - but could be the way I am looking at your table. It looks like your table lists annual leave taken and then deducts it from the total 20 day allowance, but I can't see where it calculates how much holiday is earned from a person's start date to their leaving date. Many apologies if I am being obtuse!!

[bart hoekstra]

=IF(AND($A4<>"",$B4<>"",ISERROR(SEARCH("BH",C4,1))),B4-A4+1-INT((WEEKDAY($A4-1)-$A4+$B4)/7)-INT((WEEKDAY($A4-7)-$A4+$B4)/7),)
$a4=startDate $B4=enddate
You must leave out BH(BankHoliday) ......[ISERROR(SEARCH("BH",C4,1))] .....!
My intention was to let you know the formula that takes SAterday and SUnday into account.
To help you further I need to know the Holiday conditions e.g.: 20/52 Holidays per week? or 20/365 Holidays per day?

bart

[CameronSmart]

Does this mean that each day worked has an equal weighting towards gaining holiday days? For example if you work 200 days you obtain 20 days holiday, if you work 100 days you obtain 10 days holiday ect... In this case it would mean each day worked would generate 1/10th of a holiday day. So you could look at it that way.

Find the generic fraction, or, you can set up a formula to obtain the fraction in a cell.

For example assuming there are 255 working days in a year I have made a wee example where you can see the formulas used. There is also rounding cells showing the number of days holiday rounded up (and rounded down if you decide to be mean) if that is what you are looking for.

Sorry it is a bit boring, hopefully I haven't misread your problem.

Regards

Cameron

[CameronSmart]

Sorry I didn't see that Bart had replied. I used a slightly convoluted route because I thought you wanted it to look like the layout you suggested. But Bart's method is more direct.

Sorry again

Cameron

[Calamity98]

Thanks very much Bart. I am sorry it has taken me so long to reply to you but I have had to go abroad. This is a great help. Much appreciated.

[Calamity98]

Thanks very much Cameron. I am sorry it has taken me so long to respond but I had to go abroad. This has really helped me a lot and I can show this to my colleagues and they can use it very easily. Much appreciated.