tracking accrued medical and sick leave

[BeteB]

I am relatively new to Excel. I am trying to create a spreadsheet that will track employee absences for the non-profit organization I work for. I am using a template that I got from the Microsoft Template Gallery and trying to modify it to meet our needs. It can be found at http://officeupdate.microsoft.com/T...,289,288,&RC=4&M=14&mh=20&qu=&ct=&cid=0.75.77.

Sick and Personal leave may be carried over for not more than three years. Full-time employees are entitled to six (6) days sick leave each year with pay after 3 months of work. Sick leave may be accumulated for a period of not more than three years and maximum accumulated sick leave shall be eighteen (18)days. Full-time employees are entitled for four (4) days personal leave each year with pay after three months of work. Personal leave days may be accumulated for a period of not more than three years, and the maximum accumulated leave shall be twelve (12) days. For purposes of this policy, the anniversary date of hire shall be the determining year.

There is no problem with vacation days. They cannot be carried over.

Right now, when one enters a sick or personal day it is added to the total days used which is subtracted from the total days accumulated giving one the total days left. I do not know how to deal with the three years of carry over though. That is, how can I decrease it as days are taken and drop the third year of carry over, if there is any, when year four rolls around? Is this possible to do in Excel?

I really appreciate your input. Thanks.

 

[royUK]

I have had a look at the template and I think you will need to set up a new workbook each year. Add another page for the brought forward totals and either manually enter them (Select the relevant columns and copy and paste)or use a formula (=[2001.xls]Total!$T$13) brings the balance remaining from cell T13 in a Workbook titled 2001. Each year you would have to set up a new template and delete one year's brought forward figures and add the previous years.
Change the formula in the Time allocated column to =80+Balance!G8 to add the rolled forward balances.(Balance is the name of the additional worksheet)
I can email examples if you like
This message was edited by royUK on 2002-10-15 08:02

 

[BeteB]

I figured I would manually enter the new totals each year. I guess I will have to actually look at the total used and the accrued columns (I am adding a column for carry over from 3 years ago, from 2 years ago, and from the previous year.) And then based on what the person actually used, I guess I will manually determine if they used or lost their carry over from 3 years ago—if they actually had any. I was hoping there might be a way that one could enter the days used and excel could somehow do the rest, although, I could see no way to accomplish this.

Right now we are keeping track of this stuff with pencil and paper. Do you think this spreadsheet is the way to go?

I appreciate any examples you can email me. I really want to learn this stuff. I am trying.

Thanks.

BeteB
This message was edited by BeteB on 2002-10-15 17:36