I am new to the forum -- thank you all in advance for any help you can provide.

I am working with a college course schedule that lists the start and end dates for all courses given during a specific semester. Some of the courses run for the entire semester, others only for the first 8 weeks, the last 8 weeks, and sometimes for different periods, especially if they are noncredit courses. I am trying to determine which week in the semester contains the most course meetings. Currently we identify the dates for specific weeks during the semester, as in the chart below.

[TABLE="width: 492"]

Week

[/td]Monday -

[/td]Sunday

[/td]1

[/td]Wednesday, August 15, 2012

[/td]8/19

[/td]2

[/td]8/20

[/td]8/26

[/td]3

[/td]8/27

[/td]9/2

[/td]4

[/td]9/3

[/td]9/9

[/td]5

[/td]9/10

[/td]9/16

[/td]6

[/td]9/17

[/td]9/23

[/td]7

[/td]9/24

[/td]9/30

[/td]8

[/td]10/1

[/td]10/7

[/td]9

[/td]10/8

[/td]10/14

[/td]10

[/td]10/15

[/td]10/21

[/td]11

[/td]10/22

[/td]10/28

[/td]12

[/td]10/29

[/td]11/4

[/td]13

[/td]11/5

[/td]11/11

[/td]14

[/td]11/12

[/td]11/18

[/td]15

[/td]11/19

[/td]11/25

[/td]16

[/td]11/26

[/td]12/2

[/td]17

[/td]12/3

[/td]12/9

[/td]18

[/td]12/10

[/td]Tuesday, December 11, 2012

[/td]

[/TABLE]

We then enter a new column into the course database called "Part of Term" next to the start and end date columns. Then we visually determine the range of weeks a course runs and enter it manually into the new column, e.g. Weeks 1 to 8, Weeks 6 to 8, etc.

[TABLE="width: 501"]

Course

[/td]Start Date

[/td]End Date

[/td]Part of Term

[/td]# Meeting/Week

[/td]English 101

[/td]08/15/12

[/td]12/11/12

[/td]Weeks 1 to 18

[/td]2

[/td]Math 235

[/td]08/15/12

[/td]10/07/12

[/td]Weeks 1 to 8

[/td]3

[/td]Business 134

[/td]10/08/12

[/td]12/11/12

[/td]Weeks 9 to 18

[/td]1

[/td]

[/TABLE]

[TABLE="width: 257"]

[/TABLE]

Even using filters this is a time consuming task. We also determine how many times each course meets per week and enter that data into another new column in the database titled "# Meetings/Week". Then we pull a pivot to tell us how many course meetings occur during each Part of Term. Cumbersome, but simple. The real problem comes when we try to determine which week has the most course meetings because all we have are week ranges. We have resorted to creating a table like the one below where we manually insert the number of course meetings per range into columns that represent each week of the semester. This then tells us during which week in the semester the most course meetings occur.

[TABLE="width: 1326"]

Part of Term (Weeks)

[/td]Sum of # of Meetings/ Week

[/td]Week

[/td]

[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"]9[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]11[/TD]

[TD="align: right"]12[/TD]

Weeks 1 to 18

[/td]

[TD="align: right"]4420[/TD]

1 to 16

[/td]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,420[/TD]

Weeks 1 to 8

[/td]

[TD="align: right"]330[/TD]

1 to 8

[/td]

[TD="align: right"]330[/TD]

[TD="align: right"]330[/TD]

[TD="align: right"]330[/TD]

[TD="align: right"]330[/TD]

[TD="align: right"]330[/TD]

Weeks 1 to 6

[/td]

[TD="align: right"]3[/TD]

1 to 6

[/td]

[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]

Weeks 2

[/td]

[TD="align: right"]52[/TD]

2

[/td]

[TD="align: right"]52[/TD]

Weeks 9 to 18

[/td]

[TD="align: right"]245[/TD]

9 to 16

[/td]

[TD="align: right"]245[/TD]

[TD="align: right"]245[/TD]

[TD="align: right"]245[/TD]

[TD="align: right"]245[/TD]

Weeks 10 to 16

[/td]

[TD="align: right"]8[/TD]

10 to 16

[/td]

[TD="align: right"]8[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"]8[/TD]

Grand Total

[/td]

[TD="align: right"]5058[/TD]

Total Meetings

[/td]

[TD="align: right"]4,753[/TD]

[TD="align: right"]4,805[/TD]

[TD="align: right"]4,753[/TD]

[TD="align: right"]4,753[/TD]

[TD="align: right"]4,753[/TD]

[TD="align: right"]4,423[/TD]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,420[/TD]

[TD="align: right"]4,665[/TD]

[TD="align: right"]4,673[/TD]

[TD="align: right"]4,673[/TD]

[TD="align: right"]4,673[/TD]

[/TABLE]

Many of you may be shaking your heads at our ignorance, which is why I really could use your help! Does anyone know of anyway we can simplify this procedure? We would be forever grateful!