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,

Thanks for posting in the community.

From your requirement, we need do some tests on our side, we'll appreciate it if you can understand that it will take some times to give you more effective suggestions according to the test. We will update our reply as soon as possible.

Thanks again for your understanding.

Best Regards,

George

Answer 3

Hi Courtney,

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

Regards,

George

Answer 4

I have been using the top one - where the months are laid out horizontally.  I am trying to adjust it so that certain "shifts" happen on certain days.  Not all shifts need to happen daily but there are a total of about 5 or 6 different shifts.  Does that make sense?

Thank you for your swift response!