Q
QD
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
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