Hi all,
I need help setting up calculations working with dates for a non-profit community school. I am developing a course listings database which lists courses and holds fields such as: start_date, end_date, number_of_weeks, days_of_the_week, number_of_class_sessions as well as others.
I need to generate calculations that have the ability to list all the possible dates for a course. For example, let's say there are two classes:
A mini-course which runs for 1 week based on 5 days meeting Mon, Tu, Wd, Th and Fri. Therefore, this class has 5 total class sessions.
A course which runs for 16 weeks based on twice a week which meets Mon and Wed. Therefore, this class has a total of 32 class sessions.
I have this formula to calculate the "number_of_weeks":
Int((end_date - start_date)/7)
"Day_of_the_week" = repeating field which holds all the days of the week the course can be hosted on.
"Calculate_day_of_the_week" = corresponding daily number based on "day_of_the_week" using a case formula:
Case(days_of_the_week= "Monday", 2,
etc. etc. etc. days_of_the_week= "Sunday", 1)
"Number of days" counts how frequently a course is taught within a week such as if a course is offered once a week or twice a week using this formula:
Count(days_of_the_week)
"number_of_sessions" = total number of sessions that class run such as 16 week class meeting twice a week equals 32 using:
Count(days_of_the_week) * number_of_weeks
However, I cannot seem to build a smart functionality to start with first day of class and increment by next class meeting date and continue to the end_date so I can automatically generate our class attendance roster sheets. My goal is to build layouts which can list attendance sheets based on number of weeks per course.
Is there a way to generate a formula based on number of weeks and add +7 to each date incrementing by number of weeks? Or perhaps, there is a better method? Such as if a class starts 1/5/2004 and meets for 2 weeks on Mondays and Wednesdays