Holiday Accrual Formula

Hi,

I am wondering if anybody can help with a holiday entitlement worksheet. All full time staff will have 20 days per year. However these are calculated on a monthly basis accruing 1.67 days per month.
I need a formula which will calculate this on a monthly basis also allowing me to deduct any days that may have been taken.
I also need to be able to make the formula work that if a member of staff leaves mid month eg: 15th Dec the formula will only calculate to that date and
not give me the 1.67 days as if they had worked until month end. If possible one excel sheet per month and I can copy each month. It's for 15 staff.

see example worksheet

Thanks in advance

Started 01st Oct 2017 Brought forward balance Accrued In Month Taken Balance

Joe Bloggs 01st Nov - 30th Nov 1.67 1.67 0 3.34

01st Dec - 31st Dec 3.34 1.67 2 3.01

Hi Shirley and welcome to the forum,

This is not a hard problem if you adjust the rules slightly. Instead of working with days in those "ever changing months" why not do it per year which has 365.25 days in it? If you do the months formula you also need to consider the number of days in each month AND the year which makes the problem gets much harder. See the attached where I've given each Full Time Staff (FTS) a start date and then calculated how many days they have accrued using 20 days / 365.25. This will then allow a simple number of days worked to be Today - Start Date. Note: This will be within a day (or two) of the harder calculation you want and may be exact most of the time. Then when the staff takes a day off you simply add to the bottom of the table and redo the Pivot Table. Easy Peasy?

This is like a simple check register with a running balance but the accrued amount increases each day. Hope this helps.

Staff Holidays Accrued.xlsx

My wife (the manager) thinks a lookup table using days-of-the-month should be used as it is hard to get HR to change rules. I told her you would need to test the leap year numbers also and she gave up. If the teachers union complains you could give each employee 3 extra days of employment at the start of employment which would then cover any rule changes from the old to my new method. I also wonder if the days in the first month of employment count in your method. Does the accrual only start on the first day of the next month after starting work? There are lots of ways to argue this policy. 20/365.25 is so close and much easier than working with days of each month. Adding the days of the first month worked might convince you to not need the arbitrary "3 extra day" suggestion above.