What it does
This macro allows you to build a school calendar with rotating classes or labels and display them for each month of the school year.  For example, if fun arts classes rotate in a schedule such as P.E. >> Art >> P.E. >> Music or something similar, this will build this or other types of rotations into a dynamic calendar with minimal effort.  The monthly calendars can easily be updated for unplanned closures and refreshed by simply updating the holiday listing.  Custom labels for each day can also be used if desired (manually entered for each day or applied via lookup or other formulas).

When to use it
When you need build a calendar and display different rotating classes or labels (see the screenshots below).

Why to use it
It is an extremely quick and flexible way to build out print-friendly, dynamic calendars for the entire school year and automate updates when needed.

Default shortcut
None

Other Details

• Category: Productivity / Other
• Difficulty: 3/5
• Usage/frequency: 1/5
• Automation factor: 5/5 (estimated 7,200 seconds saved each time used)
• Type: Bulk
• Date added: 3/15/2023
• Tags: School, calendar, rotation

Related Macros
None

Other Articles
None

None

Prerequisites
Have your school year calendar handy and a copy of a previous year’s rotation calendar (using this macro) if you have one.

Instructions
Run the Create School Calendar macro. This will create three settings sheets (School_Calendar_Inputs, School_Holiday_List, and School_Day_List) in a new workbook, as well as monthly calendar sheets for August through June. Fill in these yellow fields on the School_Calendar_Inputs sheet:

• First day of school (required) – enter the date for the first day of school, in a m/d/yyyy format.
• Last day of school (required) – enter the date for the last day of school, in a m/d/yyyy format.
• Rotation Group Label (required) – enter a label for the rotation the calendars should be built for.  If there is only one distinct rotation, just enter an “A” or a “1” here.  This is used to separately track different rotations, such as if one group of children goes to P.E., Art, P.E., Music (labelled rotation A) and another group goes to Art, P.E., Music, P.E. (labelled rotation B).  Make sure to change the label, the rotations (described below), and save/print a separate file for each rotation group.
• # of Rotations (required) – this is the number of items in each rotation that will be looped throughout the school year. If a rotation is P.E., Art, P.E., Music (then repeat), there are 4 rotations.
• Rotation List (required) – based on the # of rotations above, enter a label for each rotation (the required ones should be highlighted yellow), in the order they should be displayed on the calendar.  See the screenshots below for an example.

On the School_Holiday_List sheet, fill in these fields for each holiday (or day where schools are closed): 

• Date (required) – enter the date of the holiday in m/d/YYYY format.  The Day Name column will automatically update to help ensure you’ve entered the correct dates.  Make sure to enter each date within the school year that is closed.  For example, if Spring Break is a full week, enter all five dates as holidays.
• Holiday Label (required) – enter a label for each date to ensure all of them have been entered correctly.
• Skip? (required) – if the rotation should skip over holidays, enter Yes in this column (recommended for planned closures).  If the rotation should continue to run through a holiday and not skip to the next open school day, enter No in this column (possibly recommended for unplanned closures if easier).

Once the inputs and holiday dates have been entered, review each of the monthly calendar sheets to ensure everything looks correct. Then share the workbook with colleagues and/or print out for the classroom. You can also print the monthly calendar sheets to a PDF file and share with students and parents if desired.