Extending CF on WORKDAY function

[ExcelRoy]

Hi all,

I am trying to extend the Conditional Formatting from I13 to J14 but the following formula only effects I13

=WORKDAY(I13,3)<=TODAY()

Applies to is set up as I13:J13

Any help appreciated

 

[Joe4]

I am trying to extend the Conditional Formatting from I13 to J14

Applies to is set up as I13:J13

So, what do you want it extended out to? J13 or J14?
Should the CF formulas for cells J13 (and possibly J14) look at the value in column I or column J?

 

[ExcelRoy]

oh typo, sorry

I need the CF to effect I13 and J13

I have the formula in CF looking at the date in I13

Thanks

 

[Joe4]

Whenever applying the CF formula to a multi-cell range, you write the formula as it pertains to the very first cell in that multi-cell range, but you need to make sure that you use absolute/relative referencing in the appropriate places.
Since, as you move from column I to column J, you don't want the column in the formula to shift along with that, you need to lock the column reference down by placing an absolute reference sign ($) in front of the column reference in the formula, i.e.

[COLOR=#333333]=WORKDAY([/COLOR][COLOR=#ff0000][B]$[/B][/COLOR][COLOR=#333333]I13,3)<=TODAY()[/COLOR]

See this link for details on Absolute/Relative referencing: http://www.cpearson.com/excel/relative.aspx

 

[ExcelRoy]

That's great Joe4, many thanks

I have the following formula that seems to be the same but already has this method applied

=AND(LEN($I13),TODAY()+2=$I13)

 

[Joe4]

Is your value in I13 a valid date with no time component?

If so, please provide an example of a value that is not working properly.
Let us know what value the cell is in, and what the exact value in column I of that row is.

 

[ExcelRoy]

Sorry my error, all works

many thanks for your help