How to create an Attendance Reporting Calendar using an excel template

Background on Due Dates

Schools are authorized at three different levels: by a District, by the County, or by the State. Although the CDE sets a submission deadline, depending who a client is authorized by, the due date will be earlier than the CDE submission deadline.

Authorized by District:

Client School > District > County > State – P-Reports for schools authorized by a District need to be processed by the District, then the County and finally the State. Therefore, Districts set a due date much earlier than the final CDE Submission deadline.

Authorized by County:

Client School > County > State –  P-Reports for schools authorized by a County need to be processed by the County and then the State. Therefore, the county will set a due date earlier than the final CDE Submission deadline.

Authorized by State:

Client School > State – P-Reports for schools authorized by the State are sent directly to the state for processing. Thus, the due date is later than those schools authorized by a District or County.

For those clients who are not provided a P-Reporting Calendar by their authorizer, we need to create one to determine their cut-off dates and school months.

Refresher on P-Reporting Periods

P-1 – The reporting period for P-1 is from the first week of school until the last full school month that ends on or before December 31^st. The cut-off date would be the last in-session day of that full school month.

P-2 – The reporting period for P-2 is from the first week of school until the last full school month that ends on or before April 15^th. The cut-off date would be the last in-session day of that full school month.

P-3 – The reporting period for P-3 is from the first week of school until the last week of school. The cut-off date would be the last day of school.

Terminology

School Month: For the purposes of attendance reporting, a school month consists of four consecutive weeks. The school month always starts on a Monday regardless of the school start date. For example, if a school starts on a Wednesday, the reporting month will start on the Monday of that week. The max in session days for a school month is 20 days. The only time a school month can “exceed” four consecutive weeks is during a winter break exclusion.

Winter Break Exclusion: If at P-1 the last full school month ends a week of two before December 31^st, you have the option to exclude one or two weeks of the school’s winter break. The goal when using the winter break exclusion is to fit as many full school months as possible up until P-2.

Creating a P-Reporting Calendar

1. Obtain the school’s calendar (check their school website OR ask contact at the school for a copy).
2. Open the Calendar Template (can be located under the Attendance Folder > Templates > Calendar Template_XXXX) and SAVE AS to the Calendars Folder (Attendance XXXX-XXXX > Calendar) with the following format as the title: SchoolName_XXXX (where XXXX denotes the school year it is for ie. 1617, 1718, 1819, etc).
3. Update the SCHOOL NAME_SCHOOL YEAR on the calendar template.
4. Identify the school’s start date from the school’s calendar and enter the date next to First Day of School in the calendar template.
5. Highlight the cell for the first day of school in pink. The Monday of that week will be the first day in the reporting month. In other words, even though school starts on a Wednesday, for attendance reporting purposes, the start of the reporting month will be that Monday.
6. Using the school’s calendar, identify the school’s winter break dates and enter the date range in the calendar template next to the field Winter Break.
7. Starting with that first week of school, highlight four consecutive weeks and bold outline those first four weeks. This will be your first reporting month.
8. Merge the cells next to that month so that Month 1 shows next to that reporting month.
9. Starting the week after the last reporting month, highlight the next four consecutive weeks and bold outline those next four weeks. This will be you second reporting month.
10. Merge the cells next to that month so that Month 2 shows next to that reporting month.
11. Repeat steps 9 and 10 until you reach the last full reporting month that ends on or before December 31^st.
12. Identify the last in-session day of the last full reporting month that ends on or before December 31^st. This is your P-1 Cut-off date. Enter that date in the calendar template next to P1 cut off. Highlight that date in orange.
13. If the last school month ends on the week of December 31^st, then you will not have the option for a winter break exclusion. Continue repeating steps 9 and 10 until you reach the end of the school year. **
14. Identify the last in-session day of the last full reporting month that ends on or before April 15^th. This is your P-2 Cut-off date. Enter that date in the calendar template next to P2 cut off. Highlight that date in green.
15. The last day of school is your P-3 Cut-off date. Enter that date in the calendar template next to P3 cut off. Highlight that date in blue.
16. Using the school calendar, mark all the not-in-session days with a pound sign (#) in your calendar template.
17. Enter the school month date ranges in the space provided. Populate the # of days for that month in the space provided.

**Winter Break Exclusion

If the school might qualify to exclude one or two weeks of winter break, the month right after the P-1 cut-off might be five (5) weeks or six (6) weeks long, depending on which benefits the school more. The goal is to fit as many days at P-2 as possible. To see which would benefit the school more, starting the week after the P-1 cut-off, highlight the next five (5) weeks as one month, then keep highlighting four (4) weeks for each school month until you reach April 15^th. Note the cut-off week when using the five (5) weeks. Then, starting the week after the P-1 cut-off, highlight the next 6 weeks as one month, then keep highlighting four (4) weeks for each school month until you reach April 15. To cover all options, starting the week after the P-1 cut-off, highlight the next four (4) weeks as one month and keep highlighting four (4) weeks for each school month until you reach April 15^th. Do any of these three options fit perfectly up until April 15^th? If the first option fits best, the five (5) weeks, then you are only excluding one week from winter break. If the second option fits best, the six (6) weeks, then you are excluding two weeks from winter break. If the third option fits best, then you are not excluding any weeks from winter break.

Updating the Attendance Tracker

Once you have determined your P-1, P-2, and P-3 cut-off dates, update the Attendance Tracker with those dates.