Identify during which week within a date range the most course meetings occur

  • 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"]

    [tr]


    [td]

    Week

    [/td]


    [td]

    Monday -

    [/td]


    [td]

    Sunday

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Wednesday, August 15, 2012

    [/td]


    [td]

    8/19

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    8/20

    [/td]


    [td]

    8/26

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    8/27

    [/td]


    [td]

    9/2

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    9/3

    [/td]


    [td]

    9/9

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    9/10

    [/td]


    [td]

    9/16

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    9/17

    [/td]


    [td]

    9/23

    [/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    9/24

    [/td]


    [td]

    9/30

    [/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    10/1

    [/td]


    [td]

    10/7

    [/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td]

    10/8

    [/td]


    [td]

    10/14

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    10/15

    [/td]


    [td]

    10/21

    [/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td]

    10/22

    [/td]


    [td]

    10/28

    [/td]


    [/tr]


    [tr]


    [td]

    12

    [/td]


    [td]

    10/29

    [/td]


    [td]

    11/4

    [/td]


    [/tr]


    [tr]


    [td]

    13

    [/td]


    [td]

    11/5

    [/td]


    [td]

    11/11

    [/td]


    [/tr]


    [tr]


    [td]

    14

    [/td]


    [td]

    11/12

    [/td]


    [td]

    11/18

    [/td]


    [/tr]


    [tr]


    [td]

    15

    [/td]


    [td]

    11/19

    [/td]


    [td]

    11/25

    [/td]


    [/tr]


    [tr]


    [td]

    16

    [/td]


    [td]

    11/26

    [/td]


    [td]

    12/2

    [/td]


    [/tr]


    [tr]


    [td]

    17

    [/td]


    [td]

    12/3

    [/td]


    [td]

    12/9

    [/td]


    [/tr]


    [tr]


    [td]

    18

    [/td]


    [td]

    12/10

    [/td]


    [td]

    Tuesday, December 11, 2012

    [/td]


    [/tr]


    [/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"]

    [tr]


    [td]

    Course

    [/td]


    [td]

    Start Date

    [/td]


    [td]

    End Date

    [/td]


    [td]

    Part of Term

    [/td]


    [td]

    # Meeting/Week

    [/td]


    [/tr]


    [tr]


    [td]

    English 101

    [/td]


    [td]

    08/15/12

    [/td]


    [td]

    12/11/12

    [/td]


    [td]

    Weeks 1 to 18

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    Math 235

    [/td]


    [td]

    08/15/12

    [/td]


    [td]

    10/07/12

    [/td]


    [td]

    Weeks 1 to 8

    [/td]


    [td]

    3

    [/td]


    [/tr]


    [tr]


    [td]

    Business 134

    [/td]


    [td]

    10/08/12

    [/td]


    [td]

    12/11/12

    [/td]


    [td]

    Weeks 9 to 18

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [/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"]

    [tr]


    [td][/td]


    [td]

    Part of Term (Weeks)

    [/td]


    [td]

    Sum of # of Meetings/ Week

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [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]

    [/tr]


    [tr]


    [td][/td]


    [td]

    Weeks 1 to 18

    [/td]


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

    [td][/td]


    [td][/td]


    [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]

    [/tr]


    [tr]


    [td][/td]


    [td]

    Weeks 1 to 8

    [/td]


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

    [td][/td]


    [td][/td]


    [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]

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Weeks 1 to 6

    [/td]


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

    [td][/td]


    [td][/td]


    [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]

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Weeks 2

    [/td]


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

    [td][/td]


    [td][/td]


    [td]

    2

    [/td]


    [td][/td]


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

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Weeks 9 to 18

    [/td]


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

    [td][/td]


    [td][/td]


    [td]

    9 to 16

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


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

    [/tr]


    [tr]


    [td][/td]


    [td]

    Weeks 10 to 16

    [/td]


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

    [td][/td]


    [td][/td]


    [td]

    10 to 16

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


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

    [/tr]


    [tr]


    [td][/td]


    [td]

    Grand Total

    [/td]


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

    [td][/td]


    [td][/td]


    [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]

    [/tr]


    [/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!

  • Re: Identify during which week within a date range the most course meetings occur


    Thank you so very much, Herbds7! I haven't explored PivotCharts before (though I love PivotTables!) You have saved me countless hours and much frustration. I truly appreciate you and this forum!

  • Re: Identify during which week within a date range the most course meetings occur


    Hello again Herbds7. I've been able to follow all of the moves you've made to manipulate the course data but one and I am stumped. It looks like you might have pulled the number of simultaneous course meetings for a given date during the semester from your PivotChart (PowerPivot) tab, but I can't figure out how you did that - and that's where I'm stuck. Could you please tell me how you made that leap in the process? Thank you again for your patience.

  • Re: Identify during which week within a date range the most course meetings occur


    I suspect you don't have PowerPivot, a new feature for 2010/2013.
    See this book:
    "Microsoft Excel 2013, Building Data Models with PowerPivot"
    by Alberto Ferrari and Marco Russo
    or other "PowerPivot" books.
    Without the PowerPivot Add-In, you can still derive your desired data from sheet "Calendar"

  • Re: Identify during which week within a date range the most course meetings occur


    Thank you for introducing me to PowerPivot! I've downloaded it and started watching tutorials - and I'll check out the book you suggest. Have a great weekend!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!