Shift Work Calendar - Conditional Formatting Formula

[cpacs]

Hi, I'm trying to create a shift work calendar. I've attached an example of what I'm trying to achieve but please note it doesn't work as required...

I'd like to use the information on the "details" worksheet to control the conditional formatting on the "January" worksheet.

eg. I want the conditional formatting formula to look at the name in cell B8,B9,B10 etc. then match and look up the information from the "Details" worksheet (start date, shift pattern, shift code).

If an employee changes eg from Shift1 to Shift2 the conditional formatting colour would also need to change (four shift patterns, four different colours)

I've copied and pasted a few of my failed formula attempts below, hopefully they might help.

=IF(OR(NOT(ISNUMBER(D5)),D5<Job1_StartDate),"",IF(MID(INDIRECT("_"&$A$8),MOD(D5-Job1_StartDate,LEN(INDIRECT("_"&$A$8)))+1,1)=Job1_Shift1_Code,1,""))

=IF(OR(NOT(ISNUMBER(D5)),D5<Job1_StartDate),"",IF(MID(INDIRECT("_"&$A$11),MOD(D5-Job1_StartDate,LEN(INDIRECT("_"&$A$11)))+1,1)=Job1_Shift1_Code,1,""))

=IF(OR(NOT(ISNUMBER(d5)),d5<Job1_StartDate),"",IF(MID(INDIRECT(SUBSTITUTE($a10," ","_")),MOD(C5-Job1_StartDate,LEN(INDIRECT(SUBSTITUTE($a10," ","_"))))+1,1)=Job1_Shift1_Code,1,""))


=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,""))    - RED

=IF(OR(NOT(ISNUMBER(c5)),c5<Job2_StartDate),"",IF(MID(Job2_Pattern,MOD(c5-Job2_StartDate,LEN(Job2_Pattern))+1,1)=Job2_Shift1_Code,1,""))   - GREEN

=IF(OR(NOT(ISNUMBER(c5)),c5<Job3_StartDate),"",IF(MID(Job3_Pattern,MOD(c5-Job3_StartDate,LEN(Job3_Pattern))+1,1)=Job3_Shift1_Code,1,""))   -  BLUE


=IF(OR(NOT(ISNUMBER(D5)),D5<Job1_StartDate),"",IF(MID(INDIRECT("_"&$A$11),MOD(D5-Job1_StartDate,LEN(INDIRECT("_"&$A$11)))+1,1)=Job1_Shift1_Code,1,""))



=IF(OR(NOT(ISNUMBER(D5)),D5<Job1_StartDate),"",IF(MID(INDIRECT("_"&$A$8),MOD(D5-Job1_StartDate,LEN(INDIRECT("_"&$A$8)))+1,1)=Details!$S$2,1,""))
=IF(OR(NOT(ISNUMBER(D5)),D5<Job1_StartDate),"",IF(MID(INDIRECT("_"&$A$9),MOD(D5-Job1_StartDate,LEN(INDIRECT("_"&$A$9)))+1,1)=Job1_Shift1_Code,1,""))
=IF(OR(NOT(ISNUMBER(D5)),D5<Job1_StartDate),"",IF(MID(INDIRECT("_"&$A$10),MOD(D5-Job1_StartDate,LEN(INDIRECT("_"&$A$10)))+1,1)=Job1_Shift1_Code,1,""))
=IF(OR(NOT(ISNUMBER(D5)),D5<Job1_StartDate),"",IF(MID(INDIRECT("_"&$A$11),MOD(D5-Job1_StartDate,LEN(INDIRECT("_"&$A$11)))+1,1)=Details!$S$2,1,""))

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Shift Work Calendar - Conditional Formatting Formula
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.