Calendar view of production schedule

Gwill1983

Board Regular
Joined
Oct 24, 2018
Messages
124
Office Version
  1. 365
Platform
  1. Windows
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 NameStart DateEnd DateStatus
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!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Are you working 7 days a week? or from mon to fri?
Would something like this work?:

1704991810797.png
 
Upvote 0
For this layout

ProductionSchedule.xlsx
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
1
2Jan-5Jan-12Jan-19Jan-26Feb-2Feb-9Feb-16
3Job NameStartFinishDuration0102030405080910111215161718192223242526293031010205060708091213141516
4MTWTFMTWTFMTWTFMTWTFMTWTFMTWTFMTWTF
5Job 12024-01-112024-01-175
6Job 22024-01-122024-01-206
7Job 32024-01-122024-01-152
8Job 42024-01-032024-02-1431
9Job 52024-01-112024-02-0116
10
11
12
13
Sheet1
Cell Formulas
RangeFormula
G2,L2,Q2,V2,AA2,AF2,AK2G2=K3
H3:AO3H3=WORKDAY(G3,1)
G4:AO4G4=LEFT(UPPER(TEXT(G3,"ddd")),1)
E5:E9E5=NETWORKDAYS(C5,D5)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G5:AO16Expression=TaskCompletedtextNO
G5:AO16Expression=TaskInProgresstextNO


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

TaskInProgress:

Excel Formula:
=(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:

1704996514754.png


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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top