Get documents about "Vacation and Sick Time Electronic Employee Data Calendars
Document Sample
Vacation and Sick Time
Electronic Employee Data Calendars
Version 1.5
Luisa Pacheco
Brown University
July 2000
I. Introduction
This document describes the calendars I have created to help track the vacation and sick
time accrued and used by Brown University employees. The calendars are Excel
spreadsheets that provide a convenient way to record and display the time taken and earned
for a full fiscal year. Data are entered in a simple days, hours, and minutes format and the
spreadsheets calculate the total amount of time taken per month and the total quantity of
vacation and sick time available to the employee. These calendars maintain most of the
information found on the Employee Data Calendar provided by Stores Operations and
include an area for additional notes. The spreadsheets were created in Excel 5.0/95 and
will work on PC and Macintosh computers. If you would like to receive the spreadsheets
for earlier versions of Excel, please e-mail me at the address specified in Section III.
II. Description
A portion of a sample spreadsheet is shown in the following figure:
Vacation Calendar For Fiscal Year: 1998 - 1999
mm dd yyyy
Last Nam e: Doe First Nam e: John Date Em ployed: 7 11 1994
Bal. Brought Fwd. Day Hr Min
From FY: 97-98 4 1 15
Doe Doe Doe Doe Doe Doe Doe Doe
July August September October November December January February
Date Day Hr Min Day Hr Min Day Hr Min Day Hr Min Day Hr Min Day Hr Min Day Hr Min Day Hr Min
1
2
3
4
5
6
7
8 3 20
9 1
10
11
12
13
14
15
16
17
18
19
20
21 2
22
23
24
25
26 0.5
27
28
29
30
31
Total Tim e
1 3 20 0 5 45 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Used:
Tim e
2 2 2 2 2 2 2 2
Earned:
Balance: 4 5 25 5 7 10 7 7 10 9 7 10 11 7 10 13 7 10 15 7 10 17 7 10
Sample Vacation Calendar.
Version 1.5 Page 2 of 11 July 2000
At the top of the sheet is an area for recording the fiscal year, employee name, date
employed, and the amount of time carried forward from the previous year. Below this
information is a fiscal year calendar for recording the time taken and time earned. Each
month consists of three columns, and the number under the Date heading indicates the day
of the month. The three cells for each day of the month are used to record the days, hours,
and minutes of time taken on that particular day. For example, in the calendar shown
above, John Doe used 3 hours and 20 minutes of vacation time on July 8, 1 day of vacation
time on July 9, 2 hours of vacation time on August 21, and half a day of vacation time on
August 26, 1998. The total number of days, hours, and minutes used for each month is
indicated in the row labeled “Total Time Used:” and the amount of time granted for that
month is indicated by the entries in the “Time Earned:” row. The time available to the
employee by the end of a month is presented in the row marked “Balance:” The balance for
a given month is computed as:
Balance = (balance from previous year) + (time earned during the current and all previous months)
- (time used during the current and all previous months)
In the above example, John Doe had 4 days, 1 hour, and 15 minutes carried forward from
the previous year. During July, he used 1 day, 3 hours, and 20 minutes, and earned two
days, leaving him with 4 days, 5 hours, and 25 minutes of vacation time at the end of July.
During August, he took a total of 5 hours and 45 minutes and earned 2 days. At the end of
August, he will have 5 days, 7 hours, and 10 minutes of vacation time remaining.
Note: In all calculations, it is assumed that 1 day is 7.5 hours.
Version 1.5 Page 3 of 11 July 2000
III. Obtaining and Using the Calendars
To simplify use, one calendar is created for each employee. The procedure for obtaining
and using the Electronic Calendars is as follows:
1. Request a copy of the Electronic Calendars from Luisa Pacheco. You may send e-mail
to Luisa_Pacheco@Brown.edu or call 401/863-3377.
2. You will receive five spreadsheet files: one vacation calendar called VACATION.XLS,
one sick time calendar called SICKTIME.XLS, one workbook called EEDC.XLS
(which contains both vacation and sick time calendars in one workbook), one hourly-
employee vacation calendar called VACHR.XLS, and one hourly-employee sick time
calendar called SICHR.XLS. Be sure not to overwrite these files – they are the template
calendars and will be re-used each fiscal year. Also included is one documentation file
called README.DOC.
3. To establish a vacation calendar for an employee:
A) Start Excel and open the file VACATION.XLS.
B) Select a new name for the spreadsheet. For example, for employee John
Doe, V_DOE.XLS might be an appropriate choice.
C) From the FILE menu, chose Save As..., select the directory where the file is
to be stored, type the new file name in the File Name box, and click on the
OK button.
D) Enter the current fiscal year in the cell furthest to the left of the box next to
“Vacation Calendar For Fiscal Year: ”.
E) Enter the employee’s last name in the cell furthest to the left of the box next
to “Last Name:”.
F) Enter the employee’s first name in the cell box next to “First Name:”.
G) Enter the employee’s date employed in the month, day, and year boxes next
to “Date Employed:”.
H) Enter the previous fiscal year in the cell furthest to the left of the box next to
“Bal. Brought Fwd. From FY:”.
Version 1.5 Page 4 of 11 July 2000
I) Enter the number of days, hours, and minutes carried forward from the
previous fiscal year in the boxes labeled “Day,” “Hr,” and “Min”.
J) (OPTIONAL) Place an appropriate indication in the “Hr” cell for days that
are holidays and weekends. For example, if the Independence Day holiday
is observed on July 5, then enter “Independence” or “Holiday” in the “Hr”
cell for July 5. For weekends, enter “Saturday” and “Sunday”.
K) Enter the number of days, hours, and minutes that the employee accrues for
each applicable month in the row labeled “Time Earned:”. Please refer to
the Vacation Time Policy in your Policies & Practices Manual for detail
on vacation time accrual and guidelines for eligible employees.
L) Save the spreadsheet by clicking on the SAVE button on the toolbar or by
selecting SAVE from the FILE menu.
M) Repeat the above steps for each employee.
Version 1.5 Page 5 of 11 July 2000
4. The procedure for establishing a Sick Time Calendar is very similar to that for the
vacation calendar:
A) Start Excel and open the file SICKTIME.XLS.
B) Select a new name for the spreadsheet. For example, for employee John
Doe, S_DOE.XLS might be an appropriate choice.
C) From the FILE menu, chose Save As..., select the directory where the file is
to be stored, type the new file name in the File Name box, and click on the
OK button.
D) Enter the current fiscal year in the cell furthest to the left of the box next to
“Sick Time Calendar For Fiscal Year:”.
E) Enter the employee’s last name in the cell furthest to the left of the box next
to “Last Name:”.
F) Enter the employee’s first name in the cell box next to “First Name:”.
G) Enter the employee’s date employed in the month, day, and year boxes next
to “Date Employed:”.
H) Enter the date of the closing of the previous Sick Time calendar in the cell
furthest to the left of the box next to “Total Banked Days As Of :”. For
example, if the previous sick time year ended on June 30, 1999, then enter
06/30/99.
I) Enter the total banked of days, hours, and minutes carried forward in the
boxes labeled “Day” “Hr” “Min”.
J) (OPTIONAL) Place an appropriate indication in the “Hr” cell for days that
are holidays and weekends. For example, if the Independence Day holiday
is observed on July 5, then enter “Independence” or “Holiday” in the “Hr”
cell for July 5. For weekends, enter “Saturday” and “Sunday”.
K) Enter the number of days, hours, and minutes that the employee accrues for
each applicable month in the row labeled “Time Earned:”. Please refer to
the Sick Time Policy in your Policies & Practices Manual for detail on
sick time accrual and guidelines for eligible employees.
L) Save the spreadsheet by clicking on the SAVE button on the toolbar or by
selecting SAVE from the FILE menu.
M) Repeat the above steps for each employee.
Version 1.5 Page 6 of 11 July 2000
5. To record vacation or sick time taken by an employee:
A) Open the vacation or sick time Excel spreadsheet containing the employee’s
calendar.
B) Select the appropriate day and month and enter the amount of time used in
the appropriate cells. For example, if the employee has taken one day off,
then enter a 1 in the Day cell. To record half a day, either enter 0.5 in the
Day column or 3 in the hour column and 45 in the minute column. (Note:
Half a day is 3.75 hours, or 3 hours and 45 minutes.) To record 15 minutes,
enter 15 in the “Min” cell.
C) Save the calendar.
Version 1.5 Page 7 of 11 July 2000
IV) Vacation Days To Expire In December
As of the time of the writing of this document, University Policy states that employees
must use vacation days carried forward from previous year by December 31; otherwise
these days must be forfeited.
According to Vacation Time Policy # 30.021 in the Policies & Practices Manual
(under the heading Vacation Time Guidelines, Vacation Time Accrual):
Vacation days are earned on a fiscal year basis (July 1 to June 30). The
vacation days accrued in a fiscal year must be used by December 31 of the
following fiscal year or they will be forfeited. Thus, the maximum number
of days that can be on the department records as of January 1 is 12 (five for
employees in nonexempt positions with less than two years of service). The
days that remain on January 1 are only those accrued in the current fiscal
year, i.e., 12 days maximum (five days for employees working in
nonexempt positions with less than two years’ service). Any time in excess
of this maximum accrual is forfeited.
For example, John Doe is an employee who has 10 days, 2 hours, and 15 minutes carried
forward from the previous fiscal year. During July, he used 3 days and earned two days,
leaving him with 9 days, 2 hours, and 15 minutes of vacation time at the end of July.
During August, he took a total of 4 days and earned 2 days. For the months of September
through December he did not use any vacation time, but earned two days at the end of each
of month. At the end of December, he will have 15 days, 2 hours, and 15 minutes of
vacation time remaining. John Doe has been employed for more than 2 years and therefore
he cannot carry over more than 12 days. His excess time (3 days, 2 hours, and 15 minutes)
is thus forfeited and is recorded as vacation on January 1 (a comment indicating this is
placed in the “Notes” area). If Mr. Doe had 12 days or fewer remaining after December,
then no adjustment would be necessary. Mr. Doe’s calendar is shown below:
Version 1.5 Page 8 of 11 July 2000
Vacation Calendar For Fiscal Year: 1998 - 1999
Last Nam e: Doe First Nam e: John Date Em ployed:
Bal. Brought Fwd. Day Hr Min
From FY: 97-98 10 2 15
Doe Doe Doe Doe Doe Doe Doe
July August September October November December January
Date Day Hr Min Day Hr Min Day Hr Min Day Hr Min Day Hr Min Day Hr Min Day Hr Min
1 1 3 2 15
2 1
3
4
5
6 1
7
8
9
10
11 1
12 1
13 1
14 1
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Total Tim e
3 0 0 4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3 2 15
Used:
Tim e
2 2 2 2 2 2
Earned:
Balance: 9 2 15 7 2 15 9 2 15 11 2 15 13 2 15 15 2 15 12 0 0
NOTES:
On Decem ber 31, 1998, John Doe has 3 days, 2 hrs, and 15 m in in excess
of the perm itted 12 days and m ust forfeit this extra tim e. He was therefore
charged 3 days, 2 hrs, and 15 m in on January 1, 1999.
Sample Vacation Calendar With Excess Time Forfeited.
Version 1.5 Page 9 of 11 July 2000
V) How To change The View Size Of The Calendar
You can change the on-screen magnification factor for the calendar by doing the following:
From the VIEW menu, choose Zoom.
You will get a menu box named Zoom. Under Magnification, it lists several percentage
options. The default value for the calendars is 85%; you can alter the view size by selecting
a new value.
For example,
Select “100” if you want to view the calendar at 100% and click on the OK button
OR
Choose Custom and type in the magnification you want.
Note: The viewing magnification does not affect the printing of the calendar.
Version 1.5 Page 10 of 11 July 2000
VI) Miscellaneous
1. Please do not distribute the documentation and calendar files to new users; instead
have him/her request a copy from Luisa Pacheco as specified in Section III.
Obtaining and Using the Calendars. This will ensure that new users will receive
the most up-to-date versions, and can be informed of corrections, additions, or
future releases.
2. Please send any comments or suggestions regarding this document or the Electronic
Employee Data Calendars to Luisa Pacheco at the address/phone number specified
in Section III. Obtaining and Using the Calendars.
3. If you would like to be added to or removed from the calendar distribution, please
send an email to Luisa Pacheco as specified in Section III. Obtaining and Using
the Calendars.
4. Calendars are now reusable so you do not need to obtain a new calendar each year.
Be sure not to overwrite the template calendars as described in Section III.
5. If you have any questions about vacation and sick time policies, please call the
Benefits Office at 401/863-2141.
The documentation and software are provided AS-IS. Use the software
and information AT YOUR OWN RISK.
Version 1.5 Page 11 of 11 July 2000
Related docs
