Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
WORKDAY conditional format
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()) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
WORKDAY conditional format
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 -- Regards, Peo Sjoblom "nastech" wrote in message ... 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()) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
WORKDAY conditional format
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. -- David Biddulph "nastech" wrote in message ... 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()) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
WORKDAY conditional format
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? "Peo Sjoblom" wrote: 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 -- Regards, Peo Sjoblom "nastech" wrote in message ... 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()) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
WORKDAY conditional format
sorry, having a tired day, answer particular situation is:
=NOW()=WORKDAY(A51,3) instead of WEEKDAY |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
WORKDAY conditional format
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 -- Regards, Peo Sjoblom "nastech" wrote in message ... sorry, having a tired day, answer particular situation is: =NOW()=WORKDAY(A51,3) instead of WEEKDAY |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom format or conditional format? | Excel Discussion (Misc queries) | |||
How to create a conditional format that changes the number format | Excel Discussion (Misc queries) | |||
Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
copy conditional format to regular format | Setting up and Configuration of Excel | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) |