Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
QD QD is offline
external usenet poster
 
Posts: 12
Default Adding absences in rolling twelvemonths in a calendar

I need help.

I have created an attendance calendar for 2006 and 2007 under two
different tabs for each employee. For each day there are four rows
where the first row is a date, second row is for code for attendance
and hours (has two cells), the third row has indication if it is
considered an occasion or not based on the attendace reason in the row
4th.

11/13/06 13
TPU 8
Occ
Unexcused Absence (Whole)

Now at the bottom in summary I need to count number of Unexcused
absences based on 12 rolling months. So I need a formula for 2007
calendar in summary where based on a current date in 2007, the formula
goes back 12 months and looks for "Unexcused Absence" and count total
occurances.

For example today is March 15th 2007. So the formula will go back
twelve months, 3 months in 2007 calendar and 9 months in 2006 calendar
and look for "Unexcuse.." and count and place the number. If it sees
Unexcuse... 5 times it will place number "5" in the summary.

Can someone hlep me. I have a temporary solution but it is not the best
one. What I have done is created a table for twelve months in 2006
calendar where it copies the "unexcuse.." in the table under the date
when I enter it "Unexused..." in 2006 calendar. Then in a second column
I have a formula that looks at todays date and the date for which the
"Unexcus.." appears, if it is 12 months it deletes it. Than at the end
of the year I will copy the whole table in the same sheet with 2007
calendar. Then I have a formula for counting all "unsched" in 2007
calendar and than add sum from the table for 2006 based on the current
date. The formula for the sum of 2006 drops each day based on todays
date. So the sum for 2006 changes and it adds to the 2007 totals.


Please help simplify this by creating a formula in 2007 calendar that
goes back 12 months and looks in 2007 and 2006 calendar and cout number
of times the occurance is entered.

Thanks
QD

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Adding absences in rolling twelvemonths in a calendar

Hi

It is preferable to post your question in a single group. I responded to
your briefer question in Worksheet.functions asking for more
information.

On the basis of the information provided here, with 4 rows being used
for each Date's entry, and the data going down the column for each
person, then I assume in a full year there will be 1040 rows of data (52
weeks x 5 days x 4 rows) beginning with row 2 in each case and extending
to row 1041.

This being the case the formula to calculate the number of days in the
current year is simply
=COUNTIF(A$2:A$1041,"Unexcused Absence")
This could be placed in say cell A1044
Since there will only ever be data up to and including today's date,
this is always the answer for the current year.

To obtain a figure for rolling 12 months, then to the value outlines
above, you need the number of days that took place after today's date
one year previous, up to the end of that year.
In cell A1045 enter
=SUMPRODUCT(--(A5:A1041="Unexcused Absence"),
--(A2:A1038DATE(year(today())-1,MONTH(TODAY()),DAY(TODAY()))))

The 2 arrays are of equal length, but offset by 3 rows to account for
the reason being 3 rows below the date.
In the current year, this value will always be 0, but in the previous
year it will hold the number of days required to be added to the current
year to create a rolling year figure..

The Rolling year will be the sum of the 2 formulae so on Sheet 2007 in
cell A1046
=A1044+'Sheet 2006'!A1045


--
Regards

Roger Govier


"QD" wrote in message
oups.com...
I need help.

I have created an attendance calendar for 2006 and 2007 under two
different tabs for each employee. For each day there are four rows
where the first row is a date, second row is for code for attendance
and hours (has two cells), the third row has indication if it is
considered an occasion or not based on the attendace reason in the row
4th.

11/13/06 13
TPU 8
Occ
Unexcused Absence (Whole)

Now at the bottom in summary I need to count number of Unexcused
absences based on 12 rolling months. So I need a formula for 2007
calendar in summary where based on a current date in 2007, the formula
goes back 12 months and looks for "Unexcused Absence" and count total
occurances.

For example today is March 15th 2007. So the formula will go back
twelve months, 3 months in 2007 calendar and 9 months in 2006 calendar
and look for "Unexcuse.." and count and place the number. If it sees
Unexcuse... 5 times it will place number "5" in the summary.

Can someone hlep me. I have a temporary solution but it is not the
best
one. What I have done is created a table for twelve months in 2006
calendar where it copies the "unexcuse.." in the table under the date
when I enter it "Unexused..." in 2006 calendar. Then in a second
column
I have a formula that looks at todays date and the date for which the
"Unexcus.." appears, if it is 12 months it deletes it. Than at the
end
of the year I will copy the whole table in the same sheet with 2007
calendar. Then I have a formula for counting all "unsched" in 2007
calendar and than add sum from the table for 2006 based on the current
date. The formula for the sum of 2006 drops each day based on todays
date. So the sum for 2006 changes and it adds to the 2007 totals.


Please help simplify this by creating a formula in 2007 calendar that
goes back 12 months and looks in 2007 and 2006 calendar and cout
number
of times the occurance is entered.

Thanks
QD



  #3   Report Post  
Posted to microsoft.public.excel.misc
QD QD is offline
external usenet poster
 
Posts: 12
Default Adding absences in rolling twelvemonths in a calendar

Thank you very much. I will try this. It makes sense.
QD

Roger Govier wrote:
Hi

It is preferable to post your question in a single group. I responded to
your briefer question in Worksheet.functions asking for more
information.

On the basis of the information provided here, with 4 rows being used
for each Date's entry, and the data going down the column for each
person, then I assume in a full year there will be 1040 rows of data (52
weeks x 5 days x 4 rows) beginning with row 2 in each case and extending
to row 1041.

This being the case the formula to calculate the number of days in the
current year is simply
=COUNTIF(A$2:A$1041,"Unexcused Absence")
This could be placed in say cell A1044
Since there will only ever be data up to and including today's date,
this is always the answer for the current year.

To obtain a figure for rolling 12 months, then to the value outlines
above, you need the number of days that took place after today's date
one year previous, up to the end of that year.
In cell A1045 enter
=SUMPRODUCT(--(A5:A1041="Unexcused Absence"),
--(A2:A1038DATE(year(today())-1,MONTH(TODAY()),DAY(TODAY()))))

The 2 arrays are of equal length, but offset by 3 rows to account for
the reason being 3 rows below the date.
In the current year, this value will always be 0, but in the previous
year it will hold the number of days required to be added to the current
year to create a rolling year figure..

The Rolling year will be the sum of the 2 formulae so on Sheet 2007 in
cell A1046
=A1044+'Sheet 2006'!A1045


--
Regards

Roger Govier


"QD" wrote in message
oups.com...
I need help.

I have created an attendance calendar for 2006 and 2007 under two
different tabs for each employee. For each day there are four rows
where the first row is a date, second row is for code for attendance
and hours (has two cells), the third row has indication if it is
considered an occasion or not based on the attendace reason in the row
4th.

11/13/06 13
TPU 8
Occ
Unexcused Absence (Whole)

Now at the bottom in summary I need to count number of Unexcused
absences based on 12 rolling months. So I need a formula for 2007
calendar in summary where based on a current date in 2007, the formula
goes back 12 months and looks for "Unexcused Absence" and count total
occurances.

For example today is March 15th 2007. So the formula will go back
twelve months, 3 months in 2007 calendar and 9 months in 2006 calendar
and look for "Unexcuse.." and count and place the number. If it sees
Unexcuse... 5 times it will place number "5" in the summary.

Can someone hlep me. I have a temporary solution but it is not the
best
one. What I have done is created a table for twelve months in 2006
calendar where it copies the "unexcuse.." in the table under the date
when I enter it "Unexused..." in 2006 calendar. Then in a second
column
I have a formula that looks at todays date and the date for which the
"Unexcus.." appears, if it is 12 months it deletes it. Than at the
end
of the year I will copy the whole table in the same sheet with 2007
calendar. Then I have a formula for counting all "unsched" in 2007
calendar and than add sum from the table for 2006 based on the current
date. The formula for the sum of 2006 drops each day based on todays
date. So the sum for 2006 changes and it adds to the 2007 totals.


Please help simplify this by creating a formula in 2007 calendar that
goes back 12 months and looks in 2007 and 2006 calendar and cout
number
of times the occurance is entered.

Thanks
QD


  #4   Report Post  
Posted to microsoft.public.excel.misc
QD QD is offline
external usenet poster
 
Posts: 12
Default Adding absences in rolling twelvemonths in a calendar

Mr. Roger,

Hi, I tried the formulas out. As you indcated that for the current year
the rolling twelve month formula should always give me "0".
Unfortunately I am getting the same number as the current year's actual
absences. Any thoughts?
Thanks

Gautam

Roger Govier wrote:
Hi

It is preferable to post your question in a single group. I responded to
your briefer question in Worksheet.functions asking for more
information.

On the basis of the information provided here, with 4 rows being used
for each Date's entry, and the data going down the column for each
person, then I assume in a full year there will be 1040 rows of data (52
weeks x 5 days x 4 rows) beginning with row 2 in each case and extending
to row 1041.

This being the case the formula to calculate the number of days in the
current year is simply
=COUNTIF(A$2:A$1041,"Unexcused Absence")
This could be placed in say cell A1044
Since there will only ever be data up to and including today's date,
this is always the answer for the current year.

To obtain a figure for rolling 12 months, then to the value outlines
above, you need the number of days that took place after today's date
one year previous, up to the end of that year.
In cell A1045 enter
=SUMPRODUCT(--(A5:A1041="Unexcused Absence"),
--(A2:A1038DATE(year(today())-1,MONTH(TODAY()),DAY(TODAY()))))

The 2 arrays are of equal length, but offset by 3 rows to account for
the reason being 3 rows below the date.
In the current year, this value will always be 0, but in the previous
year it will hold the number of days required to be added to the current
year to create a rolling year figure..

The Rolling year will be the sum of the 2 formulae so on Sheet 2007 in
cell A1046
=A1044+'Sheet 2006'!A1045


--
Regards

Roger Govier


"QD" wrote in message
oups.com...
I need help.

I have created an attendance calendar for 2006 and 2007 under two
different tabs for each employee. For each day there are four rows
where the first row is a date, second row is for code for attendance
and hours (has two cells), the third row has indication if it is
considered an occasion or not based on the attendace reason in the row
4th.

11/13/06 13
TPU 8
Occ
Unexcused Absence (Whole)

Now at the bottom in summary I need to count number of Unexcused
absences based on 12 rolling months. So I need a formula for 2007
calendar in summary where based on a current date in 2007, the formula
goes back 12 months and looks for "Unexcused Absence" and count total
occurances.

For example today is March 15th 2007. So the formula will go back
twelve months, 3 months in 2007 calendar and 9 months in 2006 calendar
and look for "Unexcuse.." and count and place the number. If it sees
Unexcuse... 5 times it will place number "5" in the summary.

Can someone hlep me. I have a temporary solution but it is not the
best
one. What I have done is created a table for twelve months in 2006
calendar where it copies the "unexcuse.." in the table under the date
when I enter it "Unexused..." in 2006 calendar. Then in a second
column
I have a formula that looks at todays date and the date for which the
"Unexcus.." appears, if it is 12 months it deletes it. Than at the
end
of the year I will copy the whole table in the same sheet with 2007
calendar. Then I have a formula for counting all "unsched" in 2007
calendar and than add sum from the table for 2006 based on the current
date. The formula for the sum of 2006 drops each day based on todays
date. So the sum for 2006 changes and it adds to the 2007 totals.


Please help simplify this by creating a formula in 2007 calendar that
goes back 12 months and looks in 2007 and 2006 calendar and cout
number
of times the occurance is entered.

Thanks
QD


  #5   Report Post  
Posted to microsoft.public.excel.misc
QD QD is offline
external usenet poster
 
Posts: 12
Default Adding absences in rolling twelvemonths in a calendar

Roger,

If I take out "--" from the formula I am getting "0" now but for 2005 I
am also getting zero. It really should have a total number of absences
from Sept 4, 2005- Dec 31, 2005. Wright?

QD wrote:
Mr. Roger,

Hi, I tried the formulas out. As you indcated that for the current year
the rolling twelve month formula should always give me "0".
Unfortunately I am getting the same number as the current year's actual
absences. Any thoughts?
Thanks

Gautam

Roger Govier wrote:
Hi

It is preferable to post your question in a single group. I responded to
your briefer question in Worksheet.functions asking for more
information.

On the basis of the information provided here, with 4 rows being used
for each Date's entry, and the data going down the column for each
person, then I assume in a full year there will be 1040 rows of data (52
weeks x 5 days x 4 rows) beginning with row 2 in each case and extending
to row 1041.

This being the case the formula to calculate the number of days in the
current year is simply
=COUNTIF(A$2:A$1041,"Unexcused Absence")
This could be placed in say cell A1044
Since there will only ever be data up to and including today's date,
this is always the answer for the current year.

To obtain a figure for rolling 12 months, then to the value outlines
above, you need the number of days that took place after today's date
one year previous, up to the end of that year.
In cell A1045 enter
=SUMPRODUCT(--(A5:A1041="Unexcused Absence"),
--(A2:A1038DATE(year(today())-1,MONTH(TODAY()),DAY(TODAY()))))

The 2 arrays are of equal length, but offset by 3 rows to account for
the reason being 3 rows below the date.
In the current year, this value will always be 0, but in the previous
year it will hold the number of days required to be added to the current
year to create a rolling year figure..

The Rolling year will be the sum of the 2 formulae so on Sheet 2007 in
cell A1046
=A1044+'Sheet 2006'!A1045


--
Regards

Roger Govier


"QD" wrote in message
oups.com...
I need help.

I have created an attendance calendar for 2006 and 2007 under two
different tabs for each employee. For each day there are four rows
where the first row is a date, second row is for code for attendance
and hours (has two cells), the third row has indication if it is
considered an occasion or not based on the attendace reason in the row
4th.

11/13/06 13
TPU 8
Occ
Unexcused Absence (Whole)

Now at the bottom in summary I need to count number of Unexcused
absences based on 12 rolling months. So I need a formula for 2007
calendar in summary where based on a current date in 2007, the formula
goes back 12 months and looks for "Unexcused Absence" and count total
occurances.

For example today is March 15th 2007. So the formula will go back
twelve months, 3 months in 2007 calendar and 9 months in 2006 calendar
and look for "Unexcuse.." and count and place the number. If it sees
Unexcuse... 5 times it will place number "5" in the summary.

Can someone hlep me. I have a temporary solution but it is not the
best
one. What I have done is created a table for twelve months in 2006
calendar where it copies the "unexcuse.." in the table under the date
when I enter it "Unexused..." in 2006 calendar. Then in a second
column
I have a formula that looks at todays date and the date for which the
"Unexcus.." appears, if it is 12 months it deletes it. Than at the
end
of the year I will copy the whole table in the same sheet with 2007
calendar. Then I have a formula for counting all "unsched" in 2007
calendar and than add sum from the table for 2006 based on the current
date. The formula for the sum of 2006 drops each day based on todays
date. So the sum for 2006 changes and it adds to the 2007 totals.


Please help simplify this by creating a formula in 2007 calendar that
goes back 12 months and looks in 2007 and 2006 calendar and cout
number
of times the occurance is entered.

Thanks
QD




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Adding absences in rolling twelvemonths in a calendar

Hi Gautam

No I was Wong in what I wrote.
When I set the formulae up and tested it, I hardcoded the year in the
formula, but before posting I tried to generalise it so you wouldn't
need to set it differently for each sheet.
In its generalised form, then it will always equal the same as the year
to date.

If you change it to
=SUMPRODUCT(--(A5:A1041="Unexcused Absence"),
--(A2:A1038DATE(2006,MONTH(TODAY()),DAY(TODAY()))))
on the 2006 sheet, and change the 2006 to 2007 on the 2007 sheet, then
the 2007 sheet will show 0

--
Regards

Roger Govier


"QD" wrote in message
oups.com...
Mr. Roger,

Hi, I tried the formulas out. As you indcated that for the current
year
the rolling twelve month formula should always give me "0".
Unfortunately I am getting the same number as the current year's
actual
absences. Any thoughts?
Thanks

Gautam

Roger Govier wrote:
Hi

It is preferable to post your question in a single group. I responded
to
your briefer question in Worksheet.functions asking for more
information.

On the basis of the information provided here, with 4 rows being used
for each Date's entry, and the data going down the column for each
person, then I assume in a full year there will be 1040 rows of data
(52
weeks x 5 days x 4 rows) beginning with row 2 in each case and
extending
to row 1041.

This being the case the formula to calculate the number of days in
the
current year is simply
=COUNTIF(A$2:A$1041,"Unexcused Absence")
This could be placed in say cell A1044
Since there will only ever be data up to and including today's date,
this is always the answer for the current year.

To obtain a figure for rolling 12 months, then to the value outlines
above, you need the number of days that took place after today's date
one year previous, up to the end of that year.
In cell A1045 enter
=SUMPRODUCT(--(A5:A1041="Unexcused Absence"),
--(A2:A1038DATE(year(today())-1,MONTH(TODAY()),DAY(TODAY()))))

The 2 arrays are of equal length, but offset by 3 rows to account for
the reason being 3 rows below the date.
In the current year, this value will always be 0, but in the previous
year it will hold the number of days required to be added to the
current
year to create a rolling year figure..

The Rolling year will be the sum of the 2 formulae so on Sheet 2007
in
cell A1046
=A1044+'Sheet 2006'!A1045


--
Regards

Roger Govier


"QD" wrote in message
oups.com...
I need help.

I have created an attendance calendar for 2006 and 2007 under two
different tabs for each employee. For each day there are four rows
where the first row is a date, second row is for code for
attendance
and hours (has two cells), the third row has indication if it is
considered an occasion or not based on the attendace reason in the
row
4th.

11/13/06 13
TPU 8
Occ
Unexcused Absence (Whole)

Now at the bottom in summary I need to count number of Unexcused
absences based on 12 rolling months. So I need a formula for 2007
calendar in summary where based on a current date in 2007, the
formula
goes back 12 months and looks for "Unexcused Absence" and count
total
occurances.

For example today is March 15th 2007. So the formula will go back
twelve months, 3 months in 2007 calendar and 9 months in 2006
calendar
and look for "Unexcuse.." and count and place the number. If it
sees
Unexcuse... 5 times it will place number "5" in the summary.

Can someone hlep me. I have a temporary solution but it is not the
best
one. What I have done is created a table for twelve months in 2006
calendar where it copies the "unexcuse.." in the table under the
date
when I enter it "Unexused..." in 2006 calendar. Then in a second
column
I have a formula that looks at todays date and the date for which
the
"Unexcus.." appears, if it is 12 months it deletes it. Than at the
end
of the year I will copy the whole table in the same sheet with 2007
calendar. Then I have a formula for counting all "unsched" in 2007
calendar and than add sum from the table for 2006 based on the
current
date. The formula for the sum of 2006 drops each day based on
todays
date. So the sum for 2006 changes and it adds to the 2007 totals.


Please help simplify this by creating a formula in 2007 calendar
that
goes back 12 months and looks in 2007 and 2006 calendar and cout
number
of times the occurance is entered.

Thanks
QD




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
rolling twelvemnoths formula for attendance calendar QD Excel Worksheet Functions 2 September 3rd 06 08:32 AM
How do I create a rolling average chart, adding most recent data? Doug@NxEdge Charts and Charting in Excel 1 November 5th 05 03:22 AM
calculate using rolling calendar Martha Excel Worksheet Functions 1 October 10th 05 09:35 PM
Rolling Calendar MrBen via OfficeKB.com Excel Worksheet Functions 0 August 3rd 05 02:31 PM
adding letters in a payroll calendar ryanjh79 Excel Discussion (Misc queries) 6 December 17th 04 07:36 PM


All times are GMT +1. The time now is 12:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"