Sick and Vacation spreadsheet question

latwell

New Member
Joined
Mar 9, 2012
Messages
4
Hello! I'm new to the website, but have found many great answer here also. I am hoping someone might be able to provide me with some insight.


I have been creating an excel spreadsheet for a client to track used sick and vacation days. At the end of the year, they carry over any unused sick and vacation days. There is a summary sheet that shows the prior year vacation that is to be carried over (starting with 2011), followed by the annual addition, less the days used and followed by the days remaining.

The spreadsheet I have is great, but only for the current year. When 2012 my client will need to carry over the 2012 unused totals to 2013.

On a seperate page, I was wondering if I can list all the years like so:
2011
2012
2013
2014
.. and so on... with a formula next to it that would have the remaining vacation days left that are used for the following year.


Basically, I am trying to make it so that my client doesn't have to type in the carryover days to lower the rate of input errors. (The client had carried over the wrong number of days because they were handwriting items before and she shorted someone 17 sick days).

Thank you in advance for the help!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Data includes:

A tab for each employee. The individual employees tab includes:
<TABLE style="WIDTH: 94pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=125><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><TBODY><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 9.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl101 height=13 width=125 colSpan=2>January Days Used</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 height=13>Sick</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 align=right>0</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 height=13>Vacation</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 align=right>0</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 height=13>Personal</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 align=right>0</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 height=13>Floater</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 align=right>0</TD></TR></TBODY></TABLE>
There is a calander next to the table that the human resources person inputs the day off and the table above totals the items for the month.

On another page for the employee, there is a summary. The summary looks something like:

2011 Vacation Days Carryover 24 days
Vacation days accural 15 days
^^ The year changes automatically when the calendar year is changed in another section of the excel file.


Below is a table that is a Year to date summary for 2012:
<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=202><COLGROUP><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 104pt; HEIGHT: 9.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=13 width=138>PY Vacation, Carryover</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 width=64 align=right>24</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=13>Yearly accrual</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 align=right>15</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=13>Total Vacation</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 align=right>39</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=13>Days Used</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 align=right>0</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=13>Days Remaining</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 align=right>39</TD></TR></TBODY></TABLE>
^^ I want to take the "days remaining" for 2012 and have them carry over when we go to 2013.


Pretty much the only place the client really has to enter anything is the calendar. There is another sheet that has the calendar year, if the client types in a specific year, the calendars change to reflect the correct dates in correspondence to the day.


Now that I'm thinking about it more thuroughly, I don't really believe I will be able to accomplish what I am thinking of haha.
 
Upvote 0
Still having trouble grasping what the layout of the workbook is. Can you upload the workbook (with sample data) on a fileshare site and post the link?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top