Calendar view of production schedule

[Gwill1983]

Hi all,

I am trying to create a production schedule with a calendar view to make it easy for other departments to see available slots for work to be booked in.

I have found LOADS of guides to create a dynamic calendar, but none of these cover looking up events with a date range and showing these within the calendar.

My data table looks like the below:

Job Name	Start Date	End Date	Status
JOB 1	11/01/2024​	17/01/2024​	In Progress
JOB 2	13/01/2024​	20/01/2024​	Not Started
JOB 3	13/01/2024​	15/01/2024​	Not Started
JOB 4	03/01/2024​	09/01/2024​	Complete
JOB 5	03/01/2024​	01/02/2024​	In Progress

I would like to create a dynamic calendar which is able to show these scheduled jobs and the date range that they sit within.

Does anybody have any experience with this and can point me towards a guide to get this going? I dont want to follow a dynamic calendar guide which then removes any option for looking up the jobs and date ranges!

 

[felixstraube]

Are you working 7 days a week? or from mon to fri?
Would something like this work?:

 

[Gwill1983]

Are you working 7 days a week? or from mon to fri?
Would something like this work?:

View attachment 104807

We work 5 days a week (Mon - Fri).

That looks incredible! Are you able to share how you achieved it?

 

[felixstraube]

For this layout

ProductionSchedule.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1
2						Jan-5					Jan-12					Jan-19					Jan-26					Feb-2					Feb-9					Feb-16
3	Job Name	Start	Finish	Duration		01	02	03	04	05	08	09	10	11	12	15	16	17	18	19	22	23	24	25	26	29	30	31	01	02	05	06	07	08	09	12	13	14	15	16
4						M	T	W	T	F	M	T	W	T	F	M	T	W	T	F	M	T	W	T	F	M	T	W	T	F	M	T	W	T	F	M	T	W	T	F
5	Job 1	2024-01-11	2024-01-17	5
6	Job 2	2024-01-12	2024-01-20	6
7	Job 3	2024-01-12	2024-01-15	2
8	Job 4	2024-01-03	2024-02-14	31
9	Job 5	2024-01-11	2024-02-01	16
10
11
12
13
Sheet1

Cell Formulas
Range	Formula
G2,L2,Q2,V2,AA2,AF2,AK2	G2	=K3
H3:AO3	H3	=WORKDAY(G3,1)
G4:AO4	G4	=LEFT(UPPER(TEXT(G3,"ddd")),1)
E5:E9	E5	=NETWORKDAYS(C5,D5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G5:AO16	Expression	=TaskCompleted	text	NO
G5:AO16	Expression	=TaskInProgress	text	NO

We select cell G5 and then create the following named range:

TaskInProgress:

=(Sheet1!G$3>=Sheet1!$C5)*(Sheet1!G$3<=Sheet1!$D5)

Assuming that your sheet's name is "Sheet1".

Then we create a Conditional formatting rule like this:

If you want to add more jobs just add them anywhere bellow the existing ones. If you want to extend the dates to the right just select the last 5 columns and drag to the right.

Here is the working book to download:

ProductionSchedule.zip