Attendance database programming

[AdministrationIntern]

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.

[Alf Bryn]

Not sure I understood your problem righ but perhaps you could do something
like this.

Sub Auto_Open()

'
Dim i As Integer
Application.DisplayAlerts = False
For i = Sheets.Count To 12 Step -1

If i > 13 Then
Sheets(i).Delete
End If
Next i
Application.DisplayAlerts = True

End Sub

This assumes that the oldest sheet is the last one in the row of sheets.

"AdministrationIntern" <AdministrationIntern@discussions.microsoft.com>
wrote in message news:894BB417-5D84-4BF1-899B-66AFD9B16C61@microsoft.com...
>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.
>

[AdministrationIntern]

Alf,

Thanks for your reply. Not quite sure I understand your recommendations
because I am VERY new to Excel.

Let me clarify how the database is set up. Currently, there are 5
worksheets. The first is a "Year-to-Date". The next four worksheets are for
each quarter "Qtr1" "Qtr2" "Qtr3" and "Qtr4".

The policy is going to begin July 14th, so in Qtr1 worksheet, it begins on
July 14th and goes until October 13th. Qtr2 worksheet begins with October
14th and goes until Jan 13th. I have my dates going across the top, and the
employee names going down the side. The "Year-to-Date" worksheet sums up the
four quarters.

So my question is whether or not I'll be able to sum absences for a 12 month
basis. So in July of 2006, the number of absences from June 05 would not be
counted. Similarly, in August of 2006, the number of absences from July 05
would not be counted.

Does this make sense?

Thank you in advance for any help that you may be able to give.

-Angie

"Alf Bryn" wrote:

> Not sure I understood your problem righ but perhaps you could do something
> like this.
>
> Sub Auto_Open()
>
> '
> Dim i As Integer
> Application.DisplayAlerts = False
> For i = Sheets.Count To 12 Step -1
>
> If i > 13 Then
> Sheets(i).Delete
> End If
> Next i
> Application.DisplayAlerts = True
>
> End Sub
>
> This assumes that the oldest sheet is the last one in the row of sheets.
>
> "AdministrationIntern" <AdministrationIntern@discussions.microsoft.com>
> wrote in message news:894BB417-5D84-4BF1-899B-66AFD9B16C61@microsoft.com...
> >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.
> >
>
>
>

[Alf Bryn]

Thanks for clarification. I see now that did not understand what you wanted
to do.

I'm sorry that I can't help you with your problem. My advice to you if you
don't get any help is to repost your problem in 3 to 4 days describing in
detail what you want to do.

A cople of quick questions. The date column in the "Q1" is this dayly basis?
I.e first date column is July 14th, the next July 15th and so forth or is it
done in some other formate?

After a year do you plan to uppdate "Q1" with "new" data or are you adding a
new sheet?

"AdministrationIntern" <AdministrationIntern@discussions.microsoft.com>
wrote in message news:CFCCC627-F19C-44A7-BB57-38886BE04C07@microsoft.com...
> Alf,
>
> Thanks for your reply. Not quite sure I understand your recommendations
> because I am VERY new to Excel.
>
> Let me clarify how the database is set up. Currently, there are 5
> worksheets. The first is a "Year-to-Date". The next four worksheets are
> for
> each quarter "Qtr1" "Qtr2" "Qtr3" and "Qtr4".
>
> The policy is going to begin July 14th, so in Qtr1 worksheet, it begins on
> July 14th and goes until October 13th. Qtr2 worksheet begins with October
> 14th and goes until Jan 13th. I have my dates going across the top, and
> the
> employee names going down the side. The "Year-to-Date" worksheet sums up
> the
> four quarters.
>
> So my question is whether or not I'll be able to sum absences for a 12
> month
> basis. So in July of 2006, the number of absences from June 05 would not
> be
> counted. Similarly, in August of 2006, the number of absences from July 05
> would not be counted.
>
> Does this make sense?
>
> Thank you in advance for any help that you may be able to give.
>
> -Angie
>
> "Alf Bryn" wrote:
>
>> Not sure I understood your problem righ but perhaps you could do
>> something
>> like this.
>>
>> Sub Auto_Open()
>>
>> '
>> Dim i As Integer
>> Application.DisplayAlerts = False
>> For i = Sheets.Count To 12 Step -1
>>
>> If i > 13 Then
>> Sheets(i).Delete
>> End If
>> Next i
>> Application.DisplayAlerts = True
>>
>> End Sub
>>
>> This assumes that the oldest sheet is the last one in the row of sheets.
>>
>> "AdministrationIntern" <AdministrationIntern@discussions.microsoft.com>
>> wrote in message
>> news:894BB417-5D84-4BF1-899B-66AFD9B16C61@microsoft.com...
>> >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.
>> >
>>
>>
>>

[AdministrationIntern]

Alf,

Yes, sorry for not being clear before.

As for the date, it is a daily thing. So yes, it would proceed as 7/14/2006.
Then 7/15/2006. Then 7/16/2006.

Also, after the end of this current year, I think new worksheets would be
added. This is only for the ease of the people inputting the data - so they
wouldn't have to go and clear old information out.

Does this make sense?

-Angie


"Alf Bryn" wrote:

> Thanks for clarification. I see now that did not understand what you wanted
> to do.
>
> I'm sorry that I can't help you with your problem. My advice to you if you
> don't get any help is to repost your problem in 3 to 4 days describing in
> detail what you want to do.
>
> A cople of quick questions. The date column in the "Q1" is this dayly basis?
> I.e first date column is July 14th, the next July 15th and so forth or is it
> done in some other formate?
>
> After a year do you plan to uppdate "Q1" with "new" data or are you adding a
> new sheet?
>
> "AdministrationIntern" <AdministrationIntern@discussions.microsoft.com>
> wrote in message news:CFCCC627-F19C-44A7-BB57-38886BE04C07@microsoft.com...
> > Alf,
> >
> > Thanks for your reply. Not quite sure I understand your recommendations
> > because I am VERY new to Excel.
> >
> > Let me clarify how the database is set up. Currently, there are 5
> > worksheets. The first is a "Year-to-Date". The next four worksheets are
> > for
> > each quarter "Qtr1" "Qtr2" "Qtr3" and "Qtr4".
> >
> > The policy is going to begin July 14th, so in Qtr1 worksheet, it begins on
> > July 14th and goes until October 13th. Qtr2 worksheet begins with October
> > 14th and goes until Jan 13th. I have my dates going across the top, and
> > the
> > employee names going down the side. The "Year-to-Date" worksheet sums up
> > the
> > four quarters.
> >
> > So my question is whether or not I'll be able to sum absences for a 12
> > month
> > basis. So in July of 2006, the number of absences from June 05 would not
> > be
> > counted. Similarly, in August of 2006, the number of absences from July 05
> > would not be counted.
> >
> > Does this make sense?
> >
> > Thank you in advance for any help that you may be able to give.
> >
> > -Angie
> >
> > "Alf Bryn" wrote:
> >
> >> Not sure I understood your problem righ but perhaps you could do
> >> something
> >> like this.
> >>
> >> Sub Auto_Open()
> >>
> >> '
> >> Dim i As Integer
> >> Application.DisplayAlerts = False
> >> For i = Sheets.Count To 12 Step -1
> >>
> >> If i > 13 Then
> >> Sheets(i).Delete
> >> End If
> >> Next i
> >> Application.DisplayAlerts = True
> >>
> >> End Sub
> >>
> >> This assumes that the oldest sheet is the last one in the row of sheets.
> >>
> >> "AdministrationIntern" <AdministrationIntern@discussions.microsoft.com>
> >> wrote in message
> >> news:894BB417-5D84-4BF1-899B-66AFD9B16C61@microsoft.com...
> >> >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.
> >> >
> >>
> >>
> >>
>
>
>

[KR]

Angie-

If I were setting this up from scratch, I would probably put the dates in
Column A, and the names across the top (assuming you don't have over ~200
people).

By having all the dates (365/year) even if you don't use them all, you have
several options:
(1) you could use an employee total on a rolling 365 days, giving you a
true, accurate rolling total with a simple formula, and
(2) if you need quarterly summary data (although your date ranges seem
unusual, I've never seen a company with quarters that start in the middle of
the month) you could adjust your formula to only include the months of
interest.

You would no longer need to worry about multiple sheets, or adding sheets
each quarter and updating your formulas to reference them.

If you decide to go this route, re-post and I'd be glad to help you with the
365 day rolling formula (based on brilliant work from others in this group)

Thanks,
Keith

"AdministrationIntern" <AdministrationIntern@discussions.microsoft.com>
wrote in message news:49EB6BDF-E0DA-4F82-A843-38542547D83A@microsoft.com...
> Alf,
>
> Yes, sorry for not being clear before.
>
> As for the date, it is a daily thing. So yes, it would proceed as
7/14/2006.
> Then 7/15/2006. Then 7/16/2006.
>
> Also, after the end of this current year, I think new worksheets would be
> added. This is only for the ease of the people inputting the data - so
they
> wouldn't have to go and clear old information out.
>
> Does this make sense?
>
> -Angie
>
>
> "Alf Bryn" wrote:
>
> > Thanks for clarification. I see now that did not understand what you
wanted
> > to do.
> >
> > I'm sorry that I can't help you with your problem. My advice to you if
you
> > don't get any help is to repost your problem in 3 to 4 days describing
in
> > detail what you want to do.
> >
> > A cople of quick questions. The date column in the "Q1" is this dayly
basis?
> > I.e first date column is July 14th, the next July 15th and so forth or
is it
> > done in some other formate?
> >
> > After a year do you plan to uppdate "Q1" with "new" data or are you
adding a
> > new sheet?
> >
> > "AdministrationIntern" <AdministrationIntern@discussions.microsoft.com>
> > wrote in message
news:CFCCC627-F19C-44A7-BB57-38886BE04C07@microsoft.com...
> > > Alf,
> > >
> > > Thanks for your reply. Not quite sure I understand your
recommendations
> > > because I am VERY new to Excel.
> > >
> > > Let me clarify how the database is set up. Currently, there are 5
> > > worksheets. The first is a "Year-to-Date". The next four worksheets
are
> > > for
> > > each quarter "Qtr1" "Qtr2" "Qtr3" and "Qtr4".
> > >
> > > The policy is going to begin July 14th, so in Qtr1 worksheet, it
begins on
> > > July 14th and goes until October 13th. Qtr2 worksheet begins with
October
> > > 14th and goes until Jan 13th. I have my dates going across the top,
and
> > > the
> > > employee names going down the side. The "Year-to-Date" worksheet sums
up
> > > the
> > > four quarters.
> > >
> > > So my question is whether or not I'll be able to sum absences for a 12
> > > month
> > > basis. So in July of 2006, the number of absences from June 05 would
not
> > > be
> > > counted. Similarly, in August of 2006, the number of absences from
July 05
> > > would not be counted.
> > >
> > > Does this make sense?
> > >
> > > Thank you in advance for any help that you may be able to give.
> > >
> > > -Angie
> > >
> > > "Alf Bryn" wrote:
> > >
> > >> Not sure I understood your problem righ but perhaps you could do
> > >> something
> > >> like this.
> > >>
> > >> Sub Auto_Open()
> > >>
> > >> '
> > >> Dim i As Integer
> > >> Application.DisplayAlerts = False
> > >> For i = Sheets.Count To 12 Step -1
> > >>
> > >> If i > 13 Then
> > >> Sheets(i).Delete
> > >> End If
> > >> Next i
> > >> Application.DisplayAlerts = True
> > >>
> > >> End Sub
> > >>
> > >> This assumes that the oldest sheet is the last one in the row of
sheets.
> > >>
> > >> "AdministrationIntern"
<AdministrationIntern@discussions.microsoft.com>
> > >> wrote in message
> > >> news:894BB417-5D84-4BF1-899B-66AFD9B16C61@microsoft.com...
> > >> >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.
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >