Custom Date Table

[dphillips]

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.

Semester	Term	Start	End	Week
1	1	25/1/2021	31/1/2021	1
1	1	1/2/2021	7/2/2021	2
etc				(3 to 9)
1	1	29/3/2021	4/4/2021	10
1	1	5/4/2021	11/4/2021	Holidays
1	1	12/4/2021	18/4/2021	Holidays
1	2	19/4/2021	25/4/2021	11
1	2	26/4/2021	2/5/2021	12
etc				(13 to 18)
1	2	14/6/2021	20/6/2021	19
1	2	21/6/2021	27/6/2021	Holidays
1	2	28/6/2021	4/7/2021	Holidays
1	2	5/7/2021	11/7/2021	Holidays
2	3	12/7/2021	18/7/2021	1
2	3	19/7/2021	25/7/2021	2
etc				(3 to 9)
2	3	13/9/2021	19/9/2021	10
2	3	20/9/2021	26/9/2021	Holidays
2	3	27/9/2021	3/10/2021	Holidays
2	4	4/10/2021	10/10/2021	11
etc				(12 to 19)
2	4	6/12/2021	12/12/2021	20
2	4	13/12/2021	19/12/2021	Holidays
etc

[amitchandak]

@dphillips , Are these dates same every year.

[dphillips]

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.