Occupational sick pay problem

[mcinnes01]

Hi

I have been adding many new features and testing my OSP tool, when developing the SSP part of the tool I noticed a CRITICAL issue with the way historic sick days are counted! BUMP

Basically the way my tool currently works is that it updates the staff data everytime the tool is opened.

Occupational sickness allowance is based on years service

0-0.5 years = 0 full days/ 0 half days
0.5 -1 years = 0 full days/ 10 half days
1-2 years = 20 full days/ 20 half days
2-3 years = 30 full days/ 30 half days
3-4 years = 40 full days/ 40 half days
4-5 years = 50 full days/ 50 half days
5 years + = 60 full days/ 60 half days

My calculation for this works fine and has a nested if based on years service.

The 2 problems I have are related.

If you had been employed for exactly 1 year for example. In the first 6 months you were off 5 days and received no osp, then in the second half of the year your were off 10 days. You should get paid for 10 half days, however as the tool counts the whole of the history it would only pay you for 5 half days.

So I need to figure out a way to count only the days after the new allowance kicked in. I have made another date column on the STAFF sheet that assesses the date when your last allowance changed and need to some how use this as a cut off point for counting the days sick. Basically anything before this date should not be counted, but should still be displayed on the the input screen. Secondly this creates a problem and highlights an existing problem.

The history is anything out of the database that has an end date within the last 365 days. The problem is, is that the sick days are all counted, when in actual fact only the last 365 days should be counted. By the last 365 days I mean the last 365 days before the start date of the new sickness line you are keying for an individual.


I will attach an example workbook shortly and would really appreciate some help, I can't think how I can get my count to work like this.