Attendance Database Setup

[Angie]

I am creating an attendance database for my company.

There are 19 different departments in the company and each manager
tallies
his/her own employee attendances.

I had a spreadsheet set up, but it wasn't on a rolling calendar basis.
Then,
I was going to have the managers delete old months, but that would most

likely mess with the formulas.

So the new spreadsheet needs to be on a rolling calendar basis, so that
when
a new month comes up, the previous month from last year will drop off
in
calculations.

My file has three worksheets, one titled "Summary", one titled
"Details" and
the last "Setup".

The Summary page should tally the total absences from the year for each

employee. It serves as a quick view. The Details page is where
Managers
enter information about each individual employee, each day they are
late, or
don't show up for work. What I would like to do on the set-up page is
to
create a reference start date, so the Details page can reference this
and add
365 days...and sum the number of absences within that range. I don't
know how
to do this. How should the sheets be set up. Can someone please help?

Thank you.

 

[Gonzo]

Although this seems perfectly possible in Excel, it might be easier to
work with Access. However, here's one way you could go:

- create 1 master sheet with 1 entry per line (columns like date,
employee id, present/not present, late/on time, ...)
- create 1 sheet with the info of the employees with at least 2
columns: employee id (same as on the master sheet) and the name + any
additional columns you would like to add.
- build a pivot table based on the master sheet that links & groups all
the data in 1 table.
- next to the pivot, do a vlookup for each column on the employee
sheet. This would then be '=VLOOKUP(cell with employee id in pivot,
Employees!$a:$b, 1)'.

Seems like a difficult way, but it's quite flexible. But there's
numerous ways you can go... Check the database capabilities of Excel,
userforms in VBA to enter the data... Since the question is quite
broad, I'm giving you a broad answer

Angie schreef:

 

[Angie]

Gonzo,

Thank you for your help. Unfortunately, I cannot switch to using
Access, because the company does not have it.

Is there no way to do this in Excel? Perhaps if I clarify my question.

Currently, the way I have it set up is that there are 5 worksheets. The
first is a "Year-to-Date". The next four worksheets are for each
quarter, Qtr1, Qtr2, Qtr3,Qtr4. The date that this database will be
launched is 7/14/2006.

Qtr1 covers 7/14/2006 to 10/13/2006. Qtr 2 covers 10/14/2006 to
1/13/2007. And so on, so forth.

The "Year-to-Date" page has formulas that will sum across all four
quarters. Now my issue is to create a rolling calendar so that for
example, in August 2006, the totals from July 2005 will not be counted,
as they will be more than 1 year old. In September 2006, totals from
August 2005 will not be counted...so on and so forth.

Does that make sense?

I want my "Year-to-Date" formula to only calculate on a rolling
calendar basis.

Do you know how to do this?

Thanks in advance for your time.

-Angie