Attendance Database Setup

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.

0
7/7/2006 3:32:56 PM
excel 39879 articles. 2 followers. Follow

2 Replies
923 Views

Similar Articles

[PageSpeed] 34

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:

> 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.

0
7/7/2006 5:19:48 PM
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


Gonzo wrote:
> 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:
>
> > 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.

0
7/10/2006 3:04:31 PM
Reply:

Similar Artilces:

Unable to delete Database backup in SQL Server 2005/2008
I have serveral database servers with SQL Server 2005 and SQL Server 2008 when I create the maintenance plan to delete backup files older than 5 days old. I run the maintenance plan jobs it does not delete any backup files. The databhase servers have the following versions: SQL Server 2005 SP3 and SQL Server 2008 SP1 Please help me resolve this issue. Thanks so much for the help. Is there anything in the Server Event logs that would indicate the maintenance task is failing and if so what is it? It might be a permissions issue, or it may be the SQL Server Agent ser...

Restrict multiple database simultaneously
Is there anyway that can restrict the user can log in only one database at a time? We have some users that log in multiple databases simultaneously and used up all the available license. So other user can't get in. Thanks!! Kim Not within GP. You might want to check OmniPassword from Winthrop Development Corp (www.winthropdc.com) to see if it offers that functionality. -- Charles Allen, MVP "kka" wrote: > Is there anyway that can restrict the user can log in only one database at a > time? > We have some users that log in multiple databases simultaneously and...

growing exchange database
We have a priv1.edb of 16,5 GB. When we look in the manager of exchange the total size of all mailboxes is less then 4 GB (also in in the backupBackup Exec). We use use the command etutil /d en esinteg with no result the size of the mailbox database is still 16,5 GB. On the exchange server is installed Exchange STD Sp3 with the lates post rollup of sp3. What is filling up the database? The deleted items are 0 days !! Check your event logs for event 1221 that will show you how much white space you have in your database. Which version of Exchange? "Yuri S" <Yuri S@discussi...

Database for research site
Hi All Im creating a database for research patients where I want to create a table with Ethics information that will allow me to keep track of documents approved and documents yet to be approved; since I dont know how many documents I can expect to be added (it could be just 1 but it could also be 30), im not sure how many new fields to add in my table, I know I can do something with relationships but im really not sure how to go about this, any ideas/suggestions? Ta -- malagol Adding a fiekd for every document is WRONG. Maybe two tables like these -- tblDocument -- Doc...

setup #2
If i wanted my echange server to connect and pick up my email at www.freedomnames.co.uk how would i do it? Many Thanks Joe ...

Can databases be merged to update data?
Recently, two employees worked on the same database at two different locations on two different workstations. After we entered data into the databases, we cannot seem to merge our information back into one database. Atleast the help files didn't provide a solution here. Can anyone direct us to the appropriate newsgroup if this isn't the site for the answer we are seeking? If you can answer our question and provide us with the information to merge our databases into one of the databases, please help. Thank you in advance. Hi Mark, Working with copies of your databases, u...

Attendance Spreadsheet
I created a drop down list of children's names and plaeced the list under every month of the school year. I need a formula that will discern a child's name from the drop down list and count the number of present days ("P") and the number of absent days ("A") for each month. From there I can sum the months to get the total for the year. Hope someone can help! -- -PigFox Assuming you have this drop-down selector box in A2, names listed in A5:A100, Ps or As from B5:AF100 (to accommodate 31 days), and you want the count of Ps in B2 and As in C2, th...

Dynamics GP 9.0 Setup Checklist
The new setup checklist is great. However, for System-level setup keeps resetting to "not started" every time you log into a new company. It would make sense that this checklist, once the status is changed, reflect that change in all companies so visually it doesn't look incomplete depending on which company you log into. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this li...

Database results format problem
Hi I have an issue with a field [priceeuros] that reports out as a sinle interger, no commas, can someone help please This is the second page of the DBRW I am experimenting with, but there is a syntax problem SELECT * FROM Apmt1bed, format([priceeuros],'Currency') as fmtamount, ORDER BY PriceEuros ASC I have 1234567 and want 1,234,567 thank you for your help See http://www.spiderwebwoman.com/resources/dbrwtipsandtricks.asp#currency If you don't want the $ format, just use Format(priceeuros,'#,##0.00') as fmtamount -- ______________________________...

HQID in headquarters database
I'm hoping I can get a confirmation / clarification on the purpose of the HQID field. My understanding is that HQID is only zero in store ops databases that don't talk to Headquarters. When there is a Headquarters, the HQID value in say the item table must match the ID value in the item table of the HQ database. Right? In addition, all HQID values should also be zero in all headquarters databases, right? I'm trying to figure out why some of my HQ tables like cashier have HQID values in some rows that are greater than zero..... Thanks for any help on this - I believe H...

Attendance
Hey all, I am looking to create a attendance database. I mean to say, maintain attendance of all the employees in the office. I am not able to get a clear idea of the details necessary. Can some one please provide me with the links of some sites which provide an example of how it looks and what should it include. This is a special job given by my manager. But I can't rather go to him and ask what he wants to include. Please someone help. I am writing this mail with much hopes. Please help. (the sheet should include the details of the leave balance, and all details, so please provide a l...

recover storagegroup/databases with DPM through powershell
I want to recover some things through a script, I want to make offsite backups to an external disk (which is connected to another server), so I recover things to that location to get the loose data I've managed to get volumes (drives) working, but I'm struggling to get him to recover other things this person seems to have managed it, but I can't find out how: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.dataprotectionmanager&tid=f9bd4fcc-7c06-46db-9aee-fa735fa55dbb&cat=&lang=&cr=&sloc=&p=1 here's a...

CRM 4.0 SETUP ERRORS
Dear all, I tried Installing CRM 4.0 professional edition in my test lab on windows server 2003 sp2, i keep receiving the following errors after folloming the implementation guide step by step: 1.error: verify domain user account SPN for microsoft dynamics CRM ASP.NET application pool account.( I use domain user account with domain admins right for the service account) 2. Error: Index was outside the bound of the array 3: SQL error: an error accured while esterblishing connection to the server, when connecting to SQL server 2005. This failure may be caused by the fact that under defa...

Database Shrink
End Goal: Reduce the actual size of the sql file size. We have removed LOTS of history from the GP company and ran the SQL database shrink. Does not seem to impact the file size. How do we reduce the file size now that history is deleted? Shrinking databases is generally not a recommended procedure as it tends to create lots of physical file fragmentation, but if may, then here is what you need to do: MS SQL Server 2005: 1. Open Enterprise Manager 2. Right-click on the company database 3. Select Tasks from the menu 4. Click on Shrink 5. Click on Files 6. Select Data from the File Typ...

Quick questions on setup of 2003?
Can you install Active Directory Connector after Domain prep and Forest prep have been run? If you have run the Domain Prep and Forest prep for 2000, do need to run it for 2003? "LT" <anonymous@discussions.microsoft.com> wrote: >Can you install Active Directory Connector after Domain >prep and Forest prep have been run? Yes. >If you have run the Domain Prep and Forest prep for 2000, >do need to run it for 2003? Yes. -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm ...

Automatic Setup script
Hi all, we're using Outlook 2003 on Exchange 2003. We're running a setup script ..prf-file to setup users. However, the contacts in the personal addressbook in the outlook client are not recognized, when sending out emails. E.g. if they have a contact in their personal addressbook, it wont show up. All addresses in the Global addressbook are showing up though. Do you have to add any lines in the script? Here is what I have so far: [Outlook Address Book] ServiceName=CONTAB [Personal Address Book] ServiceName=MSPST AB NameOfPAB=PT_STRING8,0x001e3001 Path=PT_STRING8,0x001e6600 Sh...

How do I edit a record in database
in EXCEL 2000 I have a large database with customer names, address, phone no., and so on, 8 columns total. In a userform the customer name is chosen from a combobox and entered, the balance of the customer information is then pulled out of the database using "VLOOKUP" and applied to the worksheet. I now need to change some of the data for some of the customers but the user easily gets lost looking through the database. I would like to create another userform that would allow the user to find the customer and edit the phone no. or address or contact name. And then the updated info w...

System Attendant and SystemMailbox
In the course of accidentally deleting several users from AD, I noticed that I have mailboxes for 'System Attendant', 'SystemMailbox', and 'SMTP (servername)', but cannot remember if these were also default built-in users in AD that I deleted. Do these mailboxes need to have corresponding user accounts in AD, and if so, how do I re-create them properly? Thanks for your time. After running Cleanup Agent, it appears that SystemMailbox is the only mailbox that is missing and AD user account. Does anyone know how I would re-create this account in AD? "Holo20&q...

Attendance
Is there a template that I could use to track school class attendance throughout the year? Try this http://www.vertex42.com/ExcelTemplates/attendance-tracking.html "Shodan" wrote: > Is there a template that I could use to track school class attendance > throughout the year? > I have just run up a Worbook for you at:- http://www.pierrefondes.com/ Look at the first item on the home page (number 20). 1. The numbers (1 to 30 in row 1) need to be changed for your student names. 2. If a student has attended enter 1 in his / her column for the corre...

database freezing
I am having a problem with my database freezing when a duplicate value is entered in the primary field. I have 9 table. Each of them has two primary keys. If a duplicate value is entered in one of them, the database freezes. I would expect it to give me an error msg saying that I am entering a duplicate value. But this does not happen. Why might this be the case? Is there any way in which i can fix this problem? ...

Trying to use 2002 database in 2000
I am taking an access 2002 course which includes a CD with 2002 databases. I have version 2000 at home and at the office. Is there any way to open these databases? Or do I have to upgrade to 2002? Have you tried? The default file format in Access 2002 is the Access 2000 format, so you may get lucky. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Michelle" <anonymous@discussions.microsoft.com> wrote in message news:011001c4a71c$cb7e6e40$a501280a@phx.gbl... > I am taking an access 2002 course which includes a CD with > 2002 da...

HR Attendance
Just adding HR module, is there a way to update HR attendance with the current vacation in the Dynamics Payroll window? Also, can someone explain the purpose and use of the time codes? Where is the step, or process to accrue PTO in HR? Is it part of the payroll process? Thanks -- Doug Yes, use the reconcile utility in HR to do that. The time codes are used to track time for an accrual schedule. So you'd have a vacation time code, a sick time code, etc. Yes, on the payroll transaction menu is the accrual step. You need to read the Human Resource manual. -- Charles Allen, ...

virus in database
Someone on our network opened an email with virus attatched, crashing our exchange server. Now everyone gets 'Unable to open your default e-mail folders. The Microsoft Exchange Server computer is not available. Either there are network problems or the Microsoft Exchange Server computer is down for maintenance.' Our IT guy, who I don't have the most faith in, says we're pretty much screwed and that we'll have to reinstall Exhange, losing all our e-mails and contact lists. Please, does anyone know what the virus is that caused this and an alternative to solve the problem? I...

Database Design 01-05-10
I need help designing a database. I work for a non-profit company that offers a myriad of services to the public. Each customer that works with us is assigned a customer ID #. I called the first table of the database "General Information" which includes name, address, contact info, etc. In that table i used the customer ID # as the primary key. So far this is simple. However each customer does not use every service that we offer and each of these services do not have a specific ID #. Up to this point i have created tables for each of the services we offer. Each o...

Restoring database
I am trying to restore Fabrikam and running into an error. I am using GP 10 and SQL 2005. I backed up a copy of Fabrikam from the PartnerSource Virtual PC image. I am now trying to see if I can restore the “.bak” file to a copy of Fabrikam that is installed as part of a normal GP 10 install. (I am doing this because the PartnerSource version of Fabrikam is more complete than the version of Fabrikam that ships with GP 10 itself.) Unfortunately, I am not getting very far. If I try to restore from inside of GP, logged in under a different company, I simply get an “unknown error”. If I...