WORKDAY conditional format

[Guest]

hi, if this makes sense, item works in a cell, but does not within a
conditional format. error says cannot use data from a different
worksheet/book, which is not the case.

trying to hilite an item that is in spec of 3 days after date in A51. thanks

=AND(ISNUMBER(A51),WORKDAY(A51,3)<=NOW())

 

[Peo Sjoblom]

It's because WORKDAY is part of an add-in (ATP) and resides in another
workbook (funcres.xla), the only way to use WORKDAY in conditional
formatting is to put the formula on the same sheet (could be in a hidden
column for instance) and directly refer to the cell instead. So if you put
your formula (btw, unless you are comparing times change NOW to TODAY) in
hidden Z5 then use


=Z5=TRUE

 

[David Biddulph]

The problem is that the WORKDAY function is in the Analysis Toolpak add-in,
and that counts as being in a different workbook. You may be better off
deriving a formula dependent on WEEKDAY.

 

[Guest]

Hi, having a problem with my formula, thought got a TRUE & a FALSE meant
formula was correct, but both examples turned to the same true / false. what
am I doing wrong? (will do the formula in a workcell column:

A51: 2007-09-06 =WEEKDAY(A51,3)>=NOW() gets a: FALSE
B51: 2007-09-07 =WEEKDAY(B51,3)>=NOW() gets a: FALSE

just trying to see if it has been 3 workdays since date entered. thanks


must be something wrong with my formula?

 

[Guest]

sorry, having a tired day, answer particular situation is:
=NOW()>=WORKDAY(A51,3)

instead of WEEKDAY

 

[Peo Sjoblom]

I assume this formula worked? Do you have a time in A51 as well as a date?
If not change NOW() to TODAY() because if you put a date in A51 it will
default to 00:00 so it might skew what you would expect