Courses schedule calendar

[CHAVITA]

Hello everyone,

In the spreadsheet (Plan-course), I indicate the start date (course 1) and holidays.

There are 3 consecutive courses, course 1 ends and course 2 begins immediately, course 2 ends and course 3 begins immediately.

The courses are as follows:

​

Courses	Course 1​	Course 2​	Course 3​
Duration​	30 hours​	50 hours​	70 hours​
Partial test 1 at​	20 hours​	25 hours​	25 hours​
Partial test 2 at​	----​	----​	50 hours​
Final assessment​	30 hours​	50 hours​	70 hours​

Operation System: Windows 10

Excel version: 2019

In the spreadsheet (programming courses), I indicate the start date (course 1) and holidays.

Plan-course.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	YEAR	MONTH										YEAR	MONTH
2	2023	February										2023	March
3						01/02/2023	2										01/03/2023	2
4
5			MON	TUE	WED	THU	FRI	SAT	SUN					MON	TUE	WED	THU	FRI	SAT	SUN
6		Week 1	30	31	01	02	03	04	05				Week 1	27	28	01	02	03	04	05
7		Week 2	06	07	08	09	10	11	12				Week 2	06	07	08	09	10	11	12
8		Week 3	13	14	15	16	17	18	19				Week 3	13	14	15	16	17	18	19
9		Week 4	20	21	22	23	24	25	26				Week 4	20	21	22	23	24	25	26
10		Week 5	27	28	01	02	03	04	05				Week 5	27	28	29	30	31	01	02
11		Week 6	06	07	08	09	10	11	12				Week 6	03	04	05	06	07	08	09
12
13		Week 1											Week 1
14		Week 2											Week 2
15		Week 3											Week 3
16		Week 4											Week 4
17		Week 5											Week 5
18		Week 6											Week 6
19
20															Duration (hours)
21				Holidays	Feb	Mar				Start date (course 1)		13/02/2023		Course 1	30
22				National	22/02/2023	06/03/2023				Class hours per day		7		Course 2	50
23				Local	21/02/2023	07/03/2023								Course 3	70
24				Others	20/02/2023
25
26			Months
27			January
28			February
29			March
30			April
31			May
32			June
33			July
34			August
35			September
36			October
37			November
38			December
Sheet1

Cell Formulas
Range	Formula
F3	F3	=DATE($A$2,MATCH($B$2,$C$27:$C$38,0),1)
G3,R3	G3	=WEEKDAY($F$3,3)
Q3	Q3	=DATE($L$2,MATCH($M$2,$C$27:$C$38,0),1)
C6,N6	C6	=F3-G3
O6:T11,D6:I11	D6	=C6+1
N7:N11,C7:C11	C7	=I6+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N6:T11	Expression	=N6>EOMONTH(+$Q$3;0)	text	NO
N6:T11	Expression	=N6<$Q$3	text	NO
C6:I11	Expression	=C6>EOMONTH(+$F$3;0)	text	NO
C6:I11	Expression	=C6<$F$3	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=$C$27:$C$38
M2	List	=$C$27:$C$38

My questions are:

• How to put holidays in red color on the calendar?
• How to show start dates (in the grid) for each course (taking into consideration the holidays)?
• How to show in the grid the alerts that tell me what day I have to do the partial tests and the final assessments.

Thank you in advance for your kind attention.

 

[awoohaw]

How many hours per day for the courses? Does this vary with the course being taken?

 

[awoohaw]

posted in error. my apologies. I'm working on it.

 

[CHAVITA]

How many hours per day for the courses? Does this vary with the course being taken?

Hello awoohaw

Thank you for your kind attention.

The hours per day for the courses are 7 (seven). It doesn’t vary with the course being taken.
Thanks again.

 

[Fluff]

Excel version: 2019

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[CHAVITA]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hello Fluff,

Thanks for your comments.

I updated and saved my account details. I am using Office (Excel) version 2019 and the platform is windows 10.

I followed your suggestions. Thank you for your kind attention.