Conditional Formatting

[Samantha]

Hello, I have been driving myself insane for the better part of the day trying to manipulate this gantt chart. The conditional formatting is not cooperating as I would expect. I am determining calendar days by listing workdays * manpower and then eliminating weekends and holidays, That part is calculating properly. I just cannot get the conditional formatting to light up the cells with values. It would be nice is the values (crew size) was not populated for non-work days. Please tell me what I am missing? I feel like it is the date formula with the scrolling increment however I removed that before without any succuss.

 

[Gasman]

Like this you mean?

 

[Samantha]

Like this you mean?
View attachment 122406

Yes, partially. That is one of my issues, although values for today (sunday) in the chart should not populate either. The other is a formula in the conditional formatting - getting the actual work days to turn dark blue within the range. I am now suspecting that because my end date is a calculated date it is throwing off. In my sample the first project line the 3rd, 13th and 14th should all be dark blue.

 

[Gasman]

I was just thinking of making the font the same as fill, so as to hide the values.
However, might be better to amend the formula?

=IFERROR(IF(AND(Y$7>=$G11,Y$7<=$H11,AND(WEEKDAY(Y$7) <> 1,WEEKDAY(Y$7)<>7)),$I11,""),"")

 

[Samantha]

I was just thinking of making the font the same as fill, so as to hide the values.
However, might be better to amend the formula?

=IFERROR(IF(AND(Y$7>=$G11,Y$7<=$H11,AND(WEEKDAY(Y$7) <> 1,WEEKDAY(Y$7)<>7)),$I11,""),"")

Yes, it would be better on a formula level because the overall use is it to total the men at the bottom of the form. I would need to manipulate it in the same way the conditional formatting excludes weekend based on my cell value string strWkend on the second tab. Once warm weather hits we will run a Mon-Sat schedule. I played around with it and could not get it to work. Thanks for looking

 

[Gasman]

I have never been that great with CF in Excel, especially for the ranges to be used. Have to Google again each time.
I *think* you would need to add the criteria of weekday to the CF criteria as well. Just a date range is not going to work.

 

[Samantha]

Thanks, you made me think more on manipulating it that way (whiting out the values). I took your idea and just duplicated two of the conditional formatting rules so I don't white out my dates at the top and I don't need todays red line all the way down the page. It gives me the desired output visually. I tend to believe you, I need to make a more complex formula instead of naming the date range.