Assistance with Accrual and daily sick leave balances

Good Day

I am sitting with a big dilemma. I need to create a spreadsheet for accrual and balances of Sick Leave for our employees.
THe information is required rather urgently and it needs to be backdated.

The challenge I have is that the cycle for sick leave runs for 3 years per person based on certain criteria upon joining the company.
The other thing is that all employees obviously did not start their cycles at the same as they did not start work at the same time so I will most probably have different workbooks or worksheets.

Criteria below:

Sick leave entitlement


The sick leave entitlement is the number of days that an employee would normally work during a six week period, in every three-year cycle, calculated from the first day of employment.Therefore, if an employee works a five-day week, then six weeks would equate to 30 days, and the employee would therefore be entitled to 30 days sick leave on full pay in every three-year cycle.

Sick leave is not 10 days per year - sick leave is 30 days per three years. The employer may not restrict an employee to taking only 10 days sick leave per year. During the first six months of employment, the entitlement is 1 day paid sick leave for every 26 days worked, which amounts to approximately 1 day sick leave in every 5 weeks.

On the first working day of month number 7, the balance of the 30 days kicks in and is available to the employee. Therefore if the employee took no sick leave during the first six months of employment, then on the first working day of month number seven that employee would have 30 days sick leave available to last him for the balance of 2 ½ years remaining in his first 3-year cycle.

If the employee took say 4 days sick leave during the first six months of employment, then on the first working day of month number seven, that employee would have 26 days sick leave available to last him for the next 2 ½ years.

This amount can be used by the employee at any time during the next 2 ½ years, or three years, as the case may be, but when it is all used up then of course the employee has no further sick leave available until the start of the next 3 year cycle.

If the employee, for example, uses up all his available sick leave in month number 8 of the cycle, then he has no sick leave left until the commencement of the next cycle. Similarly, if the employee uses up all his available sick leave in month number 8, and then in month number 9 he resigns, the employer may not claim back any sick leave from that the employee.

Any sick leave remaining to the credit of the employee at the end of a sick leave cycle is forfeited.

Thanking anyone in advance who is clever enough to solve this one for me.
Have an awesme day

Could you upload a sample workbook, including manual entry of expected results?

Hi SharpL

It does not want to upload file, but i made a snapshot of what I basically need.
Hope it helps.
Thanks for the effort though.
Sample Excel Sick.jpg