Semester after semester, I got tired of building a course schedule in my syllabus by typing the dates of each class meeting in to Excel spreadsheet to go with the topic and assignment. Sure, it was easy work if you wanted something mind-numbing and brainless. Sometimes, I don’t mind repetitive work, but in this case, there is very little value added. Excel has an autofill function which is great for predictable patterns, but when you start doing things like trying to fill dates for classes that only meet on Monday, Wednesday, or Friday (or Tuesday, Thursday), Excel doesn’t understand. I decided it was time to just build a macro to do it for me 🙂

• In Excel (2013 in this instance), go the Developer tab and click on Visual Basic
• A Visual Basic IDE window will appear.
• From the top menu, click on Insert, then Module.
• Paste the following into the code window. A macro will be created for:
  • MakeMWFDates (For Monday, Wednesday, Friday patterns)
  • MakeTRDates (For Tuesday, Thursday patterns)
  • MakeMWDates (For Monday, Wednesday patterns)

Sub MakeMWFDates()
 Dim Start As Date
 Start = DateSerial(2015, 8, 24)
 Cells(1, 1) = Start
 Cells(2, 1) = Start + 2
 Cells(3, 1) = Start + 4
 For Row = 4 To 50 Step 3
 Cells(Row, 1) = Cells(Row - 3, 1) + 7
 Cells(Row + 1, 1) = Cells(Row - 2, 1) + 7
 Cells(Row + 2, 1) = Cells(Row - 1, 1) + 7
 Next Row
End Sub

Sub MakeTRDates()
 Dim Start As Date
 Start = DateSerial(2015, 8, 24)
 Cells(1, 1) = Start + 1
 Cells(2, 1) = Start + 3
 For Row = 4 To 50 Step 3
 Cells(Row, 1) = Cells(Row - 3, 1) + 7
 Cells(Row + 1, 1) = Cells(Row - 2, 1) + 7
 Cells(Row + 2, 1) = Cells(Row - 1, 1) + 7
 Next Row
End Sub

Sub MakeMWDates()
 Dim Start As Date
 Start = DateSerial(2015, 8, 24)
 Cells(1, 1) = Start
 Cells(2, 1) = Start + 2
 For Row = 3 To 50 Step 2
 Cells(Row, 1) = Cells(Row - 2, 1) + 7
 Cells(Row + 1, 1) = Cells(Row - 1, 1) + 7
 Next Row
End Sub

• You can change the start date for the semester by modifying the Start variable.
• You can also change how many “instances” or days you want built. I have this defaulted to 50 meeting dates in the beginning of the loop statement, but you can alter to your choosing.
• Excel may prompt you to save the workbook. Be sure to save it in “Excel Macro-enabled format”. This is denoted with the .xlsm file type.
• From the Visual Basic interface, go to Macros. Highlight the meeting pattern you want to create, then click run
  
• Your Excel worksheet should now be populated!
• Feel free to highlight the cells and format the date to your liking. I prefer to use a custom format (ddd, mm/dd) so that the date is displayed as “Mon 8/24”

I hope this is helpful. I need to get back to building syllabi now. Feel free to comment how else to improve this or modify for your liking.