Shift work Calendar Revisit

Question

I need to add multiple shifts the existing template that is found on the www. I saw the thread where George has a solution but when I follow the instructions meticulously, I get #NAME? in blank cells. Additionally, I cannot get the conditional format to work correctly. UHG!!!!

Answer 1

The conditional formatting rule is applied on cell value based on formula. You may set different colors based on cell values.

If you have 7 shift pattern, set 7 rules for it.

1. IF(OR(NOT(ISNUMBER(J5));J5<Job1_StartDate);"";:
   • This part checks if J5 is not a number (NOT(ISNUMBER(J5))) or if J5 is less than Job1_StartDate.
   • If either condition is true, it returns an empty string ("").
   • If both conditions are false, it proceeds to the next part of the formula.
2. IF(MID(Job1_Pattern;MOD(J5-Job1_StartDate;LEN(Job1_Pattern))+1;1)=Job1_Shift1_Code;1;:
   • MID(Job1_Pattern;MOD(J5-Job1_StartDate;LEN(Job1_Pattern))+1;1) extracts a single character from Job1_Pattern.
   • MOD(J5-Job1_StartDate;LEN(Job1_Pattern))+1 calculates the position within Job1_Pattern based on the difference between J5 and Job1_StartDate, ensuring it wraps around using the MOD function.
   • If the extracted character equals Job1_Shift1_Code, it returns 1.
3. IF(MID(Job1_Pattern;MOD(J5-Job1_StartDate;LEN(Job1_Pattern))+1;1)=Job1_Shift2_Code;2;:
   • Similar to the previous step, but checks if the extracted character equals Job1_Shift2_Code.
   • If true, it returns 2.
4. IF(MID(Job1_Pattern;MOD(J5-Job1_StartDate;LEN(Job1_Pattern))+1;1)=Job1_Shift3_Code;3;"")))):
   • Again, similar to the previous steps, but checks if the extracted character equals Job1_Shift3_Code.
   • If true, it returns 3.
   • If none of the conditions are met, it returns an empty string ("").

In summary, this formula determines the shift code (1, 2, or 3) based on the date in J5 and a repeating pattern defined in Job1_Pattern. If J5 is not a valid date or is before Job1_StartDate, it returns an empty string.

For job pattern, you can find cell reference in Name Manager.

Answer 2

I hope that works for you and the other issue I cannot navigate is the conditional formatting. I just cannot get my head wrapped around that long logic string. I will be online most of the day here and I hope you are still here.

I have just trial scheds in there so I know it looks rudimentary! LOL!!!!

I will need to add up to 7 shifts on this so at this point I am just trying to learn to fish.

H200 Work Shift Calendar.xlsx

Answer 3

Could you share a test workbook? Then I can help you correct it.

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.  *Please make sure you have removed any sensitive or private information in the sample file before uploading.Please understand that initial reply may not always immediately resolve the issue due to limited information. However, with your help and more detailed information, we can work together to find a solution.