Forum Discussion

dphillips's avatar
dphillips
Helper IV
5 years ago

Custom Date Table

Can someone please tell me the best way to set up a custom date table for the following situation? I am at a school and have 2 semesters per year with each semester having 2 terms.  (So 4 terms in total) We number each of the weeks in a semester from 1 through to, say, 19. Then, after the mid year holidays we start again with numbering the weeks from 1 through to, again, say 19. We also have holidays in the middle of each semester and holiday weeks are not numbered. This year school started on Wednesday 27th January 2021. So it would look something like this for this year. (See table below)

How can I automate this procedure each year and set up this custom date table once I know the term dates for the year? Is this the best way of setting up the table? Perhaps I should have every day in the year in 1 column and then assign a semester and term number and the number of the week in other columns somehow? (Rather than having a start and end column) But how could I automate this procedure. Any help would be very much appreciated.

 

SemesterTermStartEndWeek
1125/1/202131/1/20211
111/2/20217/2/20212
etc   (3 to 9)
1129/3/20214/4/202110
115/4/202111/4/2021Holidays
1112/4/202118/4/2021Holidays
1219/4/202125/4/2021

11

1226/4/20212/5/2021

12

etc   

(13 to 18)

1214/6/202120/6/2021

19

1221/6/202127/6/2021

Holidays

1228/6/20214/7/2021

Holidays

125/7/202111/7/2021

Holidays

2312/7/202118/7/2021

1

2319/7/202125/7/2021

2

etc   

(3 to 9)

2313/9/202119/9/2021

10

2320/9/202126/9/2021

Holidays

2327/9/20213/10/2021

Holidays

244/10/202110/10/2021

11

etc   

(12 to 19)

246/12/202112/12/2021

20

2413/12/202119/12/2021

Holidays

etc   

 

2 Replies

    • dphillips's avatar
      dphillips
      Helper IV

      No they will not be exactly the same each year. I have to be able to adjust them for slight changes in start dates, holiday dates etc. There will always be a 2 week holiday between Terms 1 and 2; a 3 week holiday between Terms 2 and 3 and a 2 week holiday between terms 3 and 4. Other than that term dates could change slightly eg based on Easter, Term 1 could be a 9 week term rather than 10 weeks in some years. The dates provided were accurate for this year only.