Cell color base on Odd/Even Week and Day criteria

I have been searching around and just cannot find a way to deal with this much less find a way to start. I have a column representing each date of the month. I would like the cells beneath to color grey as to whether the date is a Monday and is in an even week and if the code to the left of the employee is "EM" (which stands for even Monday). I would ultimately want the same for even week Mondays, odd week Fridays, etc. depending on the code beside the employee name.

I attached a spreadsheet that shows what I am trying to accomplish. Any assistance is greatly appreciated!!!

Maybe a conditional format formula of the form

=AND(WEEKDAY(H$3,2) = 1,ISEVEN(WEEKNUM(H$3)),$A6= "EM")

might help.

Hi,

Select all the cells from C6:AG100, then select Conditional Formatting --> New Rule... --> Use a formula to determine which cells to format, and enter this formula:

Formula:

Please Login or Register  to view this content.

I hope this helps

@Martin: your solution would only accommodate even Mondays, but doesn't consider the odd Fridays and odd Mondays shown in the example.

@excelforumcrisis: my formula will only fall apart if you use something like "OT", which could mean odd Tuesday or Thursday. Let me know if either of these scenarios are likely, and if so I will adjust my formula to accommodate for this

Those would be additional conditional formats in the style of the example or conceivably one more complex formula with an OR function.

Thank you all for helping! I chose what ajryan88 provided and it worked great! I just do not understand the formula. Can you explain it a little so I understand what it is doing? I am self taught and would very much wish to understand what you came up with.

Actually I just figured out what you did ajryan88. That was very clever and nothing I certainly would have thought of! Thanks again!

Sure - I had envisaged that the 'applies to' area would be all the columns and not just those which were Mondays.

I am glad I could help

Please don't forget to mark this thread as solved, and please click on the * next to any post(s) that were helpful to say thanks to the poster(s) and add to their reputation