How to Create an Excel Spreadsheet to Figure Out Sick Leaveby Stephanie Ellen
Microsoft Excel can replace mundane tasks with formulas and functions. One task that can streamline any manager or supervisor's job is to create an Excel spreadsheet to figure out sick leave for employees and use formulas to replace the task of manually calculating totals. Enter the amount of sick leave the employee has earned and how much the employee has used, and the spreadsheet formulas will do the rest, adding up totals and carrying sick leave totals forward every month.
Type "Vacation and Sick Leave Record" in cell E1. In cell E2, write the period that the sick leave worksheet is applicable to. For example, write "1/1/2010 to 12/30/2010."
Type the employee's name in cell A4. Optionally, type their maximum sick leave accumulation on the next line in days or hours.
Type the word "Month" in cell A6. Type the months January through December directly underneath, in the same column, with one month per row.
Type the word "Amount" in cell B6, the word "Used" in cell C6 and the word "Balance" in the cell D6.
Enter the total number of the employee's available sick leave hours in cell B7. For example, if the employee has 120 hours, write "120."
Type the following formula into cell D7: =B7-C7. Copy the formula to cells D8 to D17 by dragging the fill handle (the little black square in the bottom right corner of the cell) to cell D17.
Click on cell B8, type "=", then click on cell D7. This transfers the balance from the previous month to the "amount" column for the beginning of the second month. Drag the fill handle of cell B8 to cell B17.
- To use the spreadsheet, type in the amount of hours the employee has actually used in column "C." The spreadsheet will keep a running total of how many hours are used throughout the year.