Formula or Conditional Formatting not Performing Properly.

Let me start by stating that I believe I am in the right place with this question, but I am uncertain where exactly the problem lies.

I am working on a Gantt chart and am trying to utilize an "if" "or" and "weekday" formula to highlight non work days. I have a date field spanned vertically from row 3 - 6 and the dates start in column H and continue to column AY. The date in column H is 02/16/15, column I is 02/17/15 and so on. I am using the formula -- =IF(OR(WEEKDAY(H$3)=1,WEEKDAY(H$3)=7),1,0) -- so if the day value returned is 1 or 7 (Sunday or Saturday) the true statement reads 1 and false reads 0.

Spanning this formula across row 30 from column H - AY I get "00000110000011000001100000110000011000001100". This is the expected result 5 zero's and 2 one's per week.

The trouble is, when I apply this formula to conditional formatting to apply a color fill on the dates that fall in the weekend everything works properly until column AS and then everything after AS is color filled as if it were a weekend.

Any thoughts?

Project Overview_Need Help.xlsx

Sean May

Hi, welcome to the forum

To begin with, your CF was usingthe wrong reference...
=IF(OR(WEEKDAY(H$3)=1,WEEKDAY(H$3)=7),1,0)

2nd, you can shorten that by modifying your WEEKDAY() function to just this in your CF...
=WEEKDAY(H$3,2)<6

With regard to the shortened formula, thank you! It makes a lot of sense to shorten. I had to instead use =WEEKDAY(H$3,2)>5 so that the weekend was highlighted and not the weekday.

I had a feeling I had it the wrong way round, but it got you headed in the right directyion

Note that FC rules are bases on ONLY TRUE or FALSE, so you dont need to include your test in an IF() statement - which also produces a TRUE or FALSE result
Also, its a good idea to take not of the (generally) last argument in functions - it often gives you many more options

Anyway, Im glad we got you where you wanted to be

Originally Posted by Dilusive

This is so strange. I had checked the conditional formatting several times, not sure how the H was replaced with an O

Did you change the "applies to" range? Adjusting that range can change the formula, e.g if your range started at col A and you changed it to start at Col H then formula refs will change by 7 columns, i.e. H to O

If you change the applies to range then always check the formula afterwards