+ Reply to Thread
Results 1 to 23 of 23

Calculate sick days

  1. #1
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: Calculate sick days

    I am looking at your worksheet and have a couple of questions. First of all, it looks OK to me except that it doesn't show an "S" for one sick day. Is there anything else wrong?

    Second, I am not familiar with the concept of "waiting days". Is that the number of days the employee must be sick without company-paid time off, once for the entire year? That's what your example seems to do, but I want to be sure.

    I don't understand what "Sick days in one year" (in I2) means. Entitlement seems to be the number of CSP they are entitled to.

    Why do you want to show an "S" for only one sick day? Shouldn't it be shown as a waiting day--since you don't know the future yet? Or should the first sick day be labeled "S" and then change to WD if there is another sick day that happens later?

    Last, I don't understand at all what's going on the column labeled "Sick Date" but it doesn't seem to be referenced so I'm ignoring it.
    Last edited by 6StringJazzer; 01-27-2010 at 08:44 PM.

  3. #3
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Calculate sick days

    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

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Smile Re: Calculate sick days

    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.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    Re: Calculate sick days

    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
    Attached Files Attached Files

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: Calculate sick days

    Quote Originally Posted by Dave69rock View Post
    Thanks for your replies
    Dave,

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

  7. #7
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    Re: Calculate sick days

    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.

  8. #8
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Calculate sick days

    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
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    Re: Calculate sick days

    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

  10. #10
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: Calculate sick days

    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.)
    Attached Files Attached Files

  11. #11
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Wink Re: Calculate sick days

    Quote Originally Posted by Dave69rock View Post
    I'm away for a few days now....
    You're not sick, are you?

  12. #12
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    Re: Calculate sick days

    Hi

    Thanks for your help so far

    This is where I’m up to at the moment please find attached.

    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

    It all seems to be working ok up to now.

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

    Cheers

    Dave
    Attached Files Attached Files
    Last edited by Dave69rock; 01-30-2010 at 06:22 PM.

  13. #13
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    Re: Calculate sick days

    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.

  14. #14
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    Re: Calculate sick days

    Anyone have an idea how i can move on with this

  15. #15
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Calculate sick days

    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

  16. #16
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    Re: Calculate sick days

    Hi rylo

    Company sick pay only starts when an employee hands in a sick note of 5 days or more.

    Company sick pay is paid to all employees provided they have 1 or more years service, this is where the entitlement comes in, the more years service you have the more company sick pay days you are entitled to. If you have 1 year service then you get 10 days CSP,….. 3 years =20 & 5 years or more= 50.

    Waiting days comes into play depending on how many sick days you have taken in the last 365 days if you have no sick days in the last 12 months then 0 waiting days and you would received CSP for all your entitlement for one year since the start of your first sick day, if you have taken 1-4 sick days in the previous 12 months the you would have 1 waiting day before getting CSP….5-9 sick days and you would have 2 WD, 10 or more sick days would be 3 WD.

    The total of the previous years sick days need to be entered manually each time a sick note is received. (maybe a feeder column)

    After 12 months of your Last paid CSP sick day (working on a rolling period of 12 months).
    Everything resets.

    In My sheet “Dave went sick on 1/1/09 he had 2 W D and got 10 days CSP, any more sick days until 1/1/2010 will now be paid at SSP.



    Thanks for your time hope this clears things up

    Dave
    Attached Files Attached Files
    Last edited by Dave69rock; 02-13-2010 at 04:42 AM.

  17. #17
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    Re: Calculate sick days

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

    Dave

  18. #18
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    Re: Calculate sick days

    can anyone help

  19. #19
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: Calculate sick days

    Quote Originally Posted by Dave69rock View Post
    ....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.

  20. #20
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,461

    Re: Calculate sick days

    Forgot attachment.
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    Re: Calculate sick days

    Hi

    Sorry i mean after one year of the last sick day paid at CSP it would all start again W D CSP SSP for the first year some staff wouldn't have any waiting days but after one year of their last sick day paid at CSP they may start W D for the second year.

    i haven't looked at your sheet yet will do that now.

    Thanks again Dave
    Last edited by Dave69rock; 02-10-2010 at 03:39 PM.

  22. #22
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    Re: Calculate sick days

    Hi

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

    many thanks

    Dave

  23. #23
    Forum Contributor
    Join Date
    08-22-2008
    Location
    S-o-t
    MS-Off Ver
    Office 2000 & 2010
    Posts
    166

    Re: Calculate sick days

    Hi again

    The sheet should work on a rolling year from the last sick day that was paid CSP and not a calendar year.

    If you take a look at the attached sheet table1 shows the first year and table2 the second year table 1 shows the last sick day paid CSP as the 12/02/2009 on table2 the wd should not start again until 13/02/2010 and any sick days before this will be SSP

    Dave
    Attached Files Attached Files
    Last edited by Dave69rock; 02-10-2010 at 03:46 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1