Occupational sick pay problem

Sorry I had an external link in and I thought I had removed it and left some sickness data in for the example, I will have a fully working example in a moment...

Sorry about the delay it was a lot more work than I expected deleting the data links and making the data fictional.

This one does work

Hi Richard,

In cell B7 you insert an employee number e.g. 100003. This then populates the cells in the current details section, pending section and history section.

The sickness allowance and sickness used is found in cells c19 to d 20 and is seperated by full and half day allowances. This is based on the columns J through to P on the STAFF sheet.

To calculate a new sickness line, cell c19 to d20 on the INPUT sheet are refered to but are not what is used to create a new line. The proposed allowance takes in to account any lines keyed and saved on the output tab for that individual, which are displayed in the pending section on the input sheet. I need to make it so in both the current and proposed sick allowance sections that for all full, half and nil figures, only count sickness history back to the date which their allowance changed, or if the person has been employed for 6 years or more the history should count only 365 days back from the start date of the new line.

The proposed section will populate after you enter a start and end date in cells e7 and g7 respectively.

I think the route of my problem lies in column K on the staff sheet, as it just counts all sickness and has no date limit.

I have no idea how to count sick periods that span over the 365 day cut off or the span over the date an allowance changed

I wonder if something like this will fix the issue??

This is supposed to be an array formula but is just returning N/A at the moment, not sure what I have done wrong or if it will work

Please Login or Register  to view this content.

This doesn't work either

Please Login or Register  to view this content.

Spotted another problem that has only just started to occur.

When you enter a line in the green cells in row 7 on the INPUT sheet, if you enter a long sickness range for someone e.g. a few months, so it makes them use both full and half occ. You will see the 4 lines (2 code & 2 description lines) produced in the output section on the input sheet. This works fine and has always worked fine.

However, all of a sudden now when you click next to get it to copy this data to the OUT PUT sheet something strange is happening and it doubles the line up for example...

If someone had 50 days full allowance and 50 days half allowance

You key a line and they are off for 70 working days

So the line for full should say 50 and the line for half should say 20

This is the going on to the pend section too quickly and so it recalculates and submits a new line which then does an additional 30 on half pay and 40 on nil pay.

It needs to only copy over what is there and not update during the process

I would really appreciate any help I can get, I am training HR at the moment and the tool has stopped working.

Nightmare!

This is the next button code which copies the data to the next page, I think if it copied the whole of the output range to the output sheet in one go there would be no problem, but as I don't want it to paste blank lines as this will affect the csv import it uses a next row. I have a feeling it is this, that is making the problem.

Please Login or Register  to view this content.

Last problem fixed, any ideas on the other problems

Please Login or Register  to view this content.