Every semester, thousands of academics around the world struggle with a single, annoying problem. Ok actually we struggle with lots of problems, but this one is especially frustrating: having to re-type and re-order dates in a new syllabus for the same class you taught last semester.
I don’t claim to have solved this perfectly, but I found a way to do this a bit more easily using Excel’s built-in date-ordering functions, and then importing part of the Excel spreadsheet into word.
The main idea here is that the “class breakdown” section of your syllabus is transformed from an ordered list in MS Word to a smooth and editable Excel sheet. When you want to update the class list, you can either update the spreadsheet and copy-and-paste anew into the file, or simply edit within the word file. (Note: you could actually link the files and have one automatically update the other, and you could actually embed a fully functioning Excel Sheet in Word, but I’m keeping things relatively simple because the other two choices over-complicate matters.)
INSTRUCTIONS:
So, first thing to do is create a separate Excel File called “Syllabus Spreadsheet” or something. I’m attaching a sample Syllabus Spreadsheet that I use for my own courses.
I’m going to use the sample spreadsheet to explain the procedure. First, I created a “formatted table” in excel using this tool:
I made some nice headers and ended up with a table, the beginning of which looks like this.
In the above image we’re looking at the text of Cell C2, which is the only date you need to manually enter. For the purposes of a template, I’ll be using a MW class that starts August 23, 2017.
So, to begin, enter the date of your first class into cell C2.
Make sure the C column is formatted for some sort of date. I custom-formatted the column too because I like my dates in a specific format.
On the example spreadsheet it’s already done, but if you have your own, just select the C column, click on the dropdown menu in the “Number” menu, and select either a specific date format or click “More Number Formats.” If you choose the latter, you can type in what I have here, or play around with your own:
Now go back and click on cell C3. This is the kicker. Cells C3 through the end of the column need to have the same formula which tells them what day to show the user. For a MW class, your work is already done. For other class iterations, I’ve made a second sheet in the Syllabus Spreadsheet Template with each of the formulae.
Here’s what the C3 formula looks like for a MW class.
If you know how to code a little, you can probably figure out the other formulae, but if not, here they are (also included in the second sheet of the attached spreadsheet):
You can either type the formula directly into cell C3 of the first sheet, then copy and past C3 to the rest of the column C4-C40 or whatever. Or you can do the following:
- First, click on the cell in the second sheet (B2, B3, etc). Then, highlight everything after the apostrophe. Copy the text.
- Go back to the main sheet and click on C3. Paste the text.
- Finally, while on C3, hit copy. Then highlight C4 until however many classes you might have and paste.
QUICK SUMMARY TO THIS POINT: Enter a start date into C2, enter the correct formula into C3, copy the exact same formula onto C4:C50 (or however many you need).
With me? Great!
Last step! Highlight the cells you want in the syllabus, copy, then go to your word document and paste. You’ll notice that I use the spreadsheet to organize more things than I want the students to know, so I just copy and paste the first four columns, as you see here.
You’ll probably have to play around with spacing and font size and margins to make it fit perfectly, but it shouldn’t be too hard. Oh, be sure you “keep source formatting” when you paste, as that’s not always the default option in Word.
Ok, that’s it. There’s probably a million different ways to do this successfully, but this is my way, and it helps me avoid re-typing dates every semester.
Feel free to ask me questions in the comments! Otherwise, enjoy!
If you haven’t done so already, click here to download the Syllabus Spreadsheet Template.
John P. Slattery, PhD 