Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rolling twelvemnoths formula for attendance calendar | Excel Worksheet Functions | |||
How do I create a rolling average chart, adding most recent data? | Charts and Charting in Excel | |||
calculate using rolling calendar | Excel Worksheet Functions | |||
Rolling Calendar | Excel Worksheet Functions | |||
adding letters in a payroll calendar | Excel Discussion (Misc queries) |