Adding Shifts To Excel Template for Shift Work Calendar

Question

I am loving the Shift Work Calendar template for Excel - it's just what I needed!  I have figured out how to add more "jobs" but I don't know how to create more "shifts".  How do I create a formula to pull through the colors on the shift tracker sheet?

Answer 1

Hi Courtney,

For this template, you need to change the two sheets.

For Jobs and Shifts sheet, copy/paste a Shift 3 part, name it to Shift 4 as the screenshot. For the “Code” row in Shift 4 part, I type Z as code and rename it as the screenshot. You could also compare the Code row in different Shift parts. Change the Shift Pattern row, I add Z as screenshot 2.

Then, you need to change the Shift Word Calendar sheet.

Step1 set the Conditional Formatting for this new shift

Select the cell, click Conditional Formatting, choose Format only cells that contain, Cell Value, equal to, =4, choose the color> OK, copy the Applies to from the existing rule, paste it to our new rule, OK

Step2 change the formula:

a. Copy the formula in the first date cell as the screenshot.

Original formula:

=IF(OR(NOT(ISNUMBER(C5)),C5<Job1_StartDate),"",IF(MID(Job1_Pattern,MOD(C5-Job1_StartDate,LEN(Job1_Pattern))+1,1)=Job1_Shift1_Code,1,IF(MID(Job1_Pattern,MOD(C5-Job1_StartDate,LEN(Job1_Pattern))+1,1)=Job1_Shift2_Code,2,IF(MID(Job1_Pattern,MOD(C5-Job1_StartDate,LEN(Job1_Pattern))+1,1)=Job1_Shift3_Code,3,""))))

Add the bold part

=IF(OR(NOT(ISNUMBER(C5)),C5<Job1_StartDate),"",IF(MID(Job1_Pattern,MOD(C5-Job1_StartDate,LEN(Job1_Pattern))+1,1)=Job1_Shift1_Code,1,IF(MID(Job1_Pattern,MOD(C5-Job1_StartDate,LEN(Job1_Pattern))+1,1)=Job1_Shift2_Code,2,IF(MID(Job1_Pattern,MOD(C5-Job1_StartDate,LEN(Job1_Pattern))+1,1)=Job1_Shift3_Code,3,IF(MID(Job1_Pattern,MOD(C5-Job1_StartDate,LEN(Job1_Pattern))+1,1)=Job1_Shift4_Code,4,"")))))

b. Drag the cell to the same row cell

Repeat the step b to all the rows, for this sheet, you need to repeat 36 times.

Note: for different rows, you need to change the Job name part in the formula

For my workbook, I only apply the formula in one row for Job 1 in January. I will send my workbook via the Private Message.

Regards,

George

Answer 2

Hi Courtney,

We would like to know which kind of template do you use?

Regards,

George

I'm using the bottom calendar (month version). I've got the shift pattern in but don't understand where to define shift4_code and shift 5_code so I can do the conditional formatting. Can someone help?

Answer 3

Hi CaszM,

Since the thread is closed, we would like to suggest you post a new thread about the issue. Other moderators and members will come and help you.

Regards,

George