Excel 2003 - How to do this

[Guest]

I need to set up a vacation worksheet(s) to track vacation. I need to do one
for each employee (30 employees) and a consolidated one so the boss can see
at a glance the days off in any one month (or year).

the individual ones are not a problem because I can insert the hours taken
there. I could do a different worksheet in the same workbook for each
employee. It is the consolidated one that is giving me trouble.

The present consolidated one is a monthly one but if I use that i would have
12 different consolidated worksheets in addition to a yearly worksheet for
each employee.

Any ideas how to do this? (and if someone wants to share their spreadsheets
as examples, i would be more than grateful!)

 

[Guest]

Hi oceanmist. I'm not sure why you need individual worksheets for each
employee and a consolidation sheet. I would just use one worksheet. List
all the days - 1 thru 365, in column A, starting in A2. List each employee
in a separate column, starting in column C Put labels in the first row.
Days in A1, Month in B1 and then the employees in the first row of the next
30 columns. When an employee takes vacation, just enter the hours in the
appropriate row and column.

Column B is a helper column, which you can hide if you don't want it to show
in reports. In B2 put this formula: =Month(A2), copy down for all 365 days,
and format the number as General. At the end of each month, or whenever you
like actually, you can the use Data-Subtotals on the Month to find out how
many hours of vacation each employee has taken. This will show in July for
example, how many hours of vacation for each employee for each month, January
thru July, and will also give you a year-to-date total.

You could also include the amount of vacation allotted to each employee in
row 368 and then subtract the year-to-date total from that to show how much
remaining vacation is available. HTH

Sincerely, Michael Colvin

 

[Guest]

Oceanmist, one change. In column A put the dates, starting 1/1/06 thru
12/31/06, not just day numbers. That way column B will work.