2020-21 BCSD cycle day calendar in spreadsheet format

 2020-21 BCSD cycle day calendar in spreadsheet format

bcsd-2020-2021_Cycle_Calendar.ods

Create a custom google calendar for the Brighton Central School District cycle day calendar - with child specific activities for each cycle day

I wanted to take some notes on how I'm creating a google sub calendar for the Brighton Central School District (BCSD) cycle day calendar.  I want to import the BCSD calendar but they don't seem to have an easy way for me to do this.

First I downloaded the  2015-2016 Approved School Calendar from http://www.bcsd.org/calendar_events.cfm

Unfortunately, there is no cycle day calendar for 2015-2016 on the bcsd website yet.  So I am creating my own cycle day calendar, based on the approved 2015-2016 calendar.  I follow the bcsd web site via an rss feed, so when the cycle day calendar is released, I'll double check mine.

I start an ms excel spreadsheet with 4 columns.
Cycle Day Weekday Date-formula Date-value

Leave the Cycle Day column blank for now.

In the Date-formula column, put the first day of school.  ex. 9/1/2015

in the next row (cell C3), put the formula "=C2 + 1", without the double-quotes.  This indicates that the this cell should show the previous day plus one.  Copy this formula down, about 300 rows, until you have enough rows through to the last day of school.

In the Weekday column, put the formula "=TEXT(C2,"ddd")", without the leading and trailing double-quotes.  This indicates that the this cell should show the day of the week.

In the Date-value column, copy the dates from the Date-formula column and use the paste special command, to paste the values, not the formula.  This way you are left with dates, instead of formulas.

Now the Date-formula column can be removed.

Next, enable auto-filtering.  In the Weekday column, select all of the Saturday and Sunday dates, as we know that school does not meet on these days of the week, and don't belong on our calendar.

Delete all of these Saturday and Sunday rows.

At this point, you may want to save the listing with another name, in case you want to include the school breaks in your calendar.  Or maybe add another column, DoesNotAttend, and put a "Y" in this column for these dates.

Next, for the Cycle Day calendar, delete all of the rows for school holidays, ie, days when the person will not attend school.

Should end up with 180 rows, one for each instructional school day.  Currently I have 181 days, not sure if I missed an instructional holiday or if there is an extra day included, as a potential snow day.

Then use a formula to get the next cycle day.

I tried using just "=MOD((A2+1)" but this mod function would return a remainder of 0 when I wanted a value of 6 (6/6=1 and has a remainder of 0).

So, I am using an if statement.  If the remainder is 0, then display 6, otherwise add 1 to the previous number, divide it by 6, and display the remainder.

=IF(MOD((A2+1),6)=0,6,MOD((A2+1),6))

copy this formula down to the last row (end of school calendar).

This produces a cycle day calendar in this format:

Day Weekday Date-value

1 Tue 9/1/2015

2 Wed 9/2/2015

3 Thu 9/3/2015

4 Tue 9/8/2015

5 Wed 9/9/2015

6 Thu 9/10/2015

However, I want more, I want to put the activities that are specific to this cycle day in the subject line on the calendar.

For example:

Day 1-Child's Name-Activities that are specific to this cycle day.

So create the following columns

E                F                     G                     H             I                 J                        K

Subject Start Date Start Time End Date End Time All Day Event Description

Day 1 9/1/2015 9/1/2015 TRUE Day 1-First Child's Name-Music, Intramurals-10/20-early bus

Then create a virtual table starting in column O, with the cycle days, the kid's name, the activities for each cycle day, for each kid.  I only have 2 kids, so I created 2 activity columns, one for each kid.

Day First Child's Name Second Child's Name

1 Music, Intramurals-10/20-early bus Music-11:45-12:15

2 PE, Library, Technology, Orchestra-early bus Art-11:20-12:00

3 Music, Art, Chorus-9/24-early bus Gym-11:45-12:15

4 PE, Science, Orchestra-early bus Music-11:45-12:15

5 PE, Music, Chorus-9/24-early bus Library-11:45-12:15

6 PE, Art, Orchestra-early bus Gym-11:45-12:15

Then start populating the columns with formulas.

Subject (E2): =CONCATENATE($A$1,A2) -> Day1

Start Date (F2): =C2 -> 9/1/2015

Start Time (G2): leave blank because I am putting these entries in as all day events.

End Date (H2): =C2 -> 9/1/2015

End Time (I2): leave blank because I am putting these entries in as all day events.

All Day Event (J2): put "TRUE" without the quotes

Description (K2): =CONCATENATE(E2,"-",$P$1,"-",VLOOKUP(A2,$O$2:$P$5,2)) ->Day 1-First Child's Name-Music, Intramurals-10/20-early bus

Description (L2): =CONCATENATE(E2,"-",$Q$1,"-",VLOOKUP($A2,$O$2:$Q$5,3)) ->Day 1-Second Child's Name-Music-11:45-12:15

Then copy this data down for each row (school day).

Then copy the values for the first child to a new spreadsheet tab.

Ex.

Subject Start Date Start Time End Date End Time All Day Event

Day 1-Camden-Music 9/2/2014 9/2/2014 TRUE

Update/Remove any entries that are date specific.  For example, some Day 1 activities don't start until later in the school year, so I put the activity down for each associated cycle day, and updated the first few weeks, to remove activities that have not started yet.

Then save each calendar as a separate csv file.

Go into your google calendar.  Create a new sub calendar.  This way if you made a mistake along the way, you can just delete the sub-calendar, all at once, instead of manually deleting 180 entries.

Import this csv file.

Double check how it shows up on your google calendar, online, and in your mobile device.

I have an iphone 4s which automatically creates a reminder for all day events.  This was annoying, so I deleted this calendar from my iphone, changed the default reminder, and then re-added this calendar.

Attached are my cycle day calendars for bcsd for 2014-2015 and 2015-2016.

I am contemplating recording a youtube video for this, because these are instructions are very complex.