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,

How do you add additional Jobs? I am unable to figure that out.

Thanks

Answer 3

How do you add more jobs and get them to cross over to the calendar?

Answer 4

George -

Can you send me your 4 job workbook too? I can't seem to figure it out.

Answer 5

I am having troubke adding shifts. My shifts will represent My staff and the color will represent what time they are working.  

The prblem is that Have more than 3 staff.

Trying to add columns but not working .