struggling to conditional format, using only workdays

[brianaclark1967]

Good afternoon

I have a sheet that has order dates in column A, then column B has the lead times

In column C it shows the due date.

I'm looking for the formula to change column C, both 5 working days and 8 working days after the date in column A

Your help would be much appreciated

Sample sheet attached

Regards

Brian

 

[chirayu]

you mean this?

 

[chirayu]

WHOOPS just read u want workdays added

change red to =WORKDAY($A3,8)<TODAY()
change orange to =WORKDAYS($A3,5)<TODAY()

 

[Peter Bartholomew]

Similar idea but more in the way of setup effort.

I set the data up as a table to avoid the need for direct cell referencing and then, because it appears that conditional formats do not like structured references, I defined the names 'orderDate' and 'leadTime' to be
= Orders[@[order date]]
= Orders[@[lead time]]
and then 'red?' and 'amber?' to be
= WORKDAY( orderDate, 0.8*leadTime ) <= TODAY()
and
= WORKDAY( orderDate, 0.5*leadTime ) <= TODAY()
respectively.

Unsurprisingly, the conditional formats are based upon the formulas
= red?
= amber?

 

[brianaclark1967]

WHOOPS just read u want workdays added

change red to =WORKDAY($A3,8)<TODAY()
change orange to =WORKDAYS($A3,5)<TODAY()

Hi Chirayu

That is excellent. Thank you so much for yur help.

Regards

Brian

 

[brianaclark1967]

Similar idea but more in the way of setup effort.

I set the data up as a table to avoid the need for direct cell referencing and then, because it appears that conditional formats do not like structured references, I defined the names 'orderDate' and 'leadTime' to be
= Orders[@[order date]]
= Orders[@[lead time]]
and then 'red?' and 'amber?' to be
= WORKDAY( orderDate, 0.8*leadTime ) <= TODAY()
and
= WORKDAY( orderDate, 0.5*leadTime ) <= TODAY()
respectively.

Unsurprisingly, the conditional formats are based upon the formulas
= red?
= amber?

Thanks Peter,

I opted for the simpler solution in the end, but yours was certainly imformative, Thank you

Regards

Brian