Calculate sick days

Hi

I’ve tried formulas for this but nothing is working, and was wondering if there was some vba that would do the job

Please find attached a sample sheet of where I’m up to.

I’m trying to calculate when someone goes sick to see whether they are entitled to company sick pay (CSP) Statutory sick pay (SSP) and most people will have waiting days (W D) before qualifying for CSP.

A6:A735 have set dates for two years
D6:D735 have the sick days an employee has taken
B6:B735 Matches the sick date to the date in column A

G2 has how many days entitlement for CSP
H2 has how many waiting days (W D) before CSP can start

In Column C i need CSP, SSP, or W D next to the number 1

And if someone only has 1 day sick they would not qualify for any of the above and I just need “S” in column C.

I originally posted this in excel worksheet functions

Cheers

Dave
http://www.excelforum.com/excel-work...sick-days.html

Hi

Can you update your example file and put in column E what should appear in column C, and the reason for that particular code. Also, expand the examples so that it covers all your possible outcomes. I notice that you don't seem to have a single day in your listing, so that part can't be tested for any result.

rylo

Here is your example updated with a formula that takes care of all your cases as I understand them. I have done a couple of slightly tricky things that bear explaining. They add greatly to the readability of the formula but are not strictly necessary to solve the problem.

Here is the rearchitected formula in column C

=IF(OR($B6=0,SickToDate=0),"",IF(SUM($B$6:$B$735)=1,"S",IF(SickToDate<=WaitingDays,"W D",IF(SickToDate<=WaitingDays+Entitlement,"CSP","SSP"))))

First I need to explain the tricky bits. The name SickToDate is a named formula. This is a feature that is very useful but not commonly known. It is defined the same way you can define a named cell. This particular formula calculates the total number of sick days that been taken starting at the first day through the day where the formula is called. The names WaitingDays and Entitlement are simply the names of the cells holding those values. If this is confusing for you let me know and I can provide an alternative solution, though one that will look ugly.

I also added Freeze Panes at row 6 to make it easier to scroll down your data.

In English, here is how the formula breaks down:

If the current day is not a sick day, or there have no sick days yet this year, blank.
Otherwise, if the total sick days for the entire period is 1, show "S"
Otherwise, if the number of sick days to date is less than or equal to the number of waiting days, show "W D"
Otherwise, if the number of sick days to date is less than or equal to the total of waiting days plus entitlement days, show "CSP"
Otherwise, show "SSP"

As far as my earlier questions, I still don't understand how you are using "S" but I think what I did matches your original specification.

Thanks for your replies

6Stringjazzer

Yes waiting days are unpaid and last for a year, waiting days would resume the next year. And so would the entitlement for CSP

The column sick date is the actual days an employee is sick Column B inserts a 1 if it finds a match in that column. You can ignore it.

Hi rylo

Attached is a new sheet.

Dave

Originally Posted by Dave69rock

Thanks for your replies

Dave,

Did you notice that I attached a solution in my prior post?

I replied before i saw your post i'm looking at it now many thanks it looks good apart from the single sick day could be any time in the year after wd,csp, ssp if i put one single sick day with your formula it returns ssp.

Hi

A slightly different approach, that has some "helper" columns, and a split out of the Sick days, and OFF days.

This doesn't try to cover the change of year aspects - you would have to enhance your example file with expected results for me to try to cover that off.

See how it goes.

rylo

Thanks rylo

I'm away for a few days now so i will get back to you when i've had a chance to update a new workbook

So if someone has a single isolated sick day, it should always be completely ignored for purposes of counting Waiting Days, CSP Days, and SSP days?

Wow, that's a tough company to work for.

If so, then the formula gets a bit trickier, because you have to count days not just based on the number of sick days, but based on the number of sick days that do not have an S next to them. The easiest way to do that is to create another column, which I have labeled Cumulative Sick Days, which counts only non-S sick days. The values in this column replaced the formula SickToDate that I used the first go-round. (There might be some way to do this with array formulas, and definitely with VBA, but this was quickest to implement.)

Originally Posted by Dave69rock

I'm away for a few days now....

You're not sick, are you?

If it helps the calculate sheet can have as many feeder coulmns or pivot table as is needed

The table sheet shows how i want the results to be shown.

Anyone have an idea how i can move on with this

Hi

Update you example file with some examples that cover the change of year, provide the expected result and describe why that result should appear.

Also, exactly when does the year change start? Calendar year change, Financial year change, anniversary of some date? If so, then what date?

rylo

Is it possible for vba to do what i'm looking for

Dave

can anyone help

Originally Posted by Dave69rock

....I’ve discovered that I don’t need the single sick day they will always be blocks of 5 days it just works with WD,CSP, SSP now

....What I need is after one year of the last W D I need the waiting days,CSP & SSP days to start again.

I corrected this by making a change to the named formula SickToDate to sum the only the sick days for the current calendar year. Please have a look and see if this meets your needs.

I do not understand what you mean by "after one year of the last WD." Do you mean that the cumulative count of sick days does not start again at the beginning of the calendar year, but starts counting one year after the most recent WD? That is a trickier problem that what I just provided, but certainly doable.

Forgot attachment.

Hi

Your sheet looks just what i'm looking for i will run some more tests

many thanks

Dave