Excel conditional formatting I\'m trying to modify a scheduling spreadsheet some

Question

Excel conditional formatting

I'm trying to modify a scheduling spreadsheet someone no longer with my company created and I'm having trouble with Conditional Formatting when a referenced cell has a formula in it. The Conditional Formatting has formulas over a range that fills in the cells based on date and number of days it takes to complete a job. The "Start Date" of a job is the following formula: =WORKDAY(End Date, - Days, Holidays!$A$2:$A$9) or =WORKDAY(S3, -Q3, Holidays!$A$2:$A$9). The Conditional Formatting in the range Y3:SQ1000 is:

=AND(Y$2>=$R3,Y$2<$R3+($S3-$R3+1)*$A3%) when true it turns the cell green (this is the start date of the job)

=AND(Y$2>=$R3+($S3-$R3+1)*A3%,Y$2<=$S3) when true it turns the cell to hashmarks with green background (this is the duration of the job)

=AND(Y$2>=$V3,Y$2<$V3+($V3-$S3+1)*$A3%) when true it turns the cell black (this indicates the end of the job)

Y$2=the beginning of the calendar

$R3=Start Date of the job (see formula above begginning with =WORKDAY(S3, -Q3, Holidays!$A$2:$A$9)

$S3=End Date =WORKDAY(V3,-5,Holidays!$A$2:$A$9) V3=Customer Delivery Date

$V3=Customer Delivery Date

I have found that if I manually enter the Start Date then the Condittional Formatting works without a problem.

I would appreciate any help oor direction that can be provided.

Thank youu

Explanation / Answer

Hi,

Try these cconditional formats. Seee the bbold part.

Start date: =AND(Y$2>=$R3,Y$2<$R3+($S3-$R3+1)*(1-$A3%))

Duration: =AND(Y$2>$R3+($S3-$R3+1)*($A3%),Y$2<<=$S3)

[EDIT] Forgot to mention, turn of "Stop if True""

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.