Complex If/Or/And formula

[Ron Luzius]

I am working on a formula that has me frazzled. I can't get seem to get the
correct result for all of my scenarios.

Column G is Planned End Date
Column H is Revised End Date

This is what I have so far;
=OR(ISBLANK(G3),(WORKDAY(NOW(),0)-WORKDAY(G3,0)<7),OR((WORKDAY(H3,0)<WORKDAY(NOW(),0)),ISBLANK(H3)))

I am going to use the formula in a Conditional Formatting for Column G
If any of the "tests" are true, I will color the cell Red.

IF G3 is Blank TRUE
IF G3 < 7 days from NOW() and H3 is Blank TRUE
IF G3 < 7 days from NOW() and H3 < NOW() TRUE



--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---

 

[OssieMac]

Hello Ron,

I have not tested the following to the nth degree but if any of the
conditions do not appear to work as they should then give me an example of G3
and H3 values that do not work. I purely followed your written explanation
for the conditions.

When using OR, you can nest AND within the OR conditions when the OR
condition incorporates 2 conditions as per your explanation.

As a tip when creating these conditional formulas, enter them in a cell on a
worksheet and they return a true or false in the cell and it is easy to test
them by changing the values on the worksheet. When you have the formula
correct, highlight the formula in the formula bar then Copy and then press
Enter or Esc and you can then paste them into the conditional format formula.
(Don't forget the Enter or Esc after Copy to get out of the Formula bar or
you will have all sorts of problems.)

Also, do you really want NOW() and not TODAY(). NOW() is date and time and
TODAY() is date only?

=OR(ISBLANK(G3),AND(WORKDAY(NOW(),0)-WORKDAY(G3,0)<7,ISBLANK(H3)),AND(WORKDAY(NOW(),0)-WORKDAY(G3,0)<7,WORKDAY(H3,0)<NOW()))

 

[Shane Devenshire]

Your two discriptions of the problem are not consistant. In one case you use
WORKDAY in the other you say nothing about WORKDAY. Note that
WORKDAY(NOW(),0) just returns the current date. And likewise WORKDAY(H3,0)
just returns the date in H3. In the first case you could use just TODAY(),
no need for WORKDAY and in the second case, assuming there is a date in H3,
not a date and time, you could just use H3.

Here is a formula that seems to do what you might be looking for:

=IF(OR(G1="",AND(H1="",(TODAY()-G1)<7),AND(H1<TODAY(),(TODAY()-G1)<7)),TRUE,"")

In the conditional formatting area you would modify this to read:

=OR(G1="",AND(H1="",(TODAY()-G1)<7),AND(H1<TODAY(),(TODAY()-G1)<7))

 

[Ron Luzius]

Nope. Neither formula worked correctly.
I am gonna wrap my head with duct tape B4 it explodes!

The formula as it stands now is;

=OR(ISBLANK(G2),OR(TODAY()-WORKDAY(G2,0)<=7,ISBLANK(H2)),AND(TODAY()-WORKDAY(G2,0)<=7,OR(TODAY()>WORKDAY(H2,0),WORKDAY((H2),0)<=TODAY())))

Conditionals
IF G2 is Blank
or
IF G2 <= 7 Workdays from Today() and H2 is Blank
or
IF G2 <= Today() and H2 <= 7 Workdays from Today()

G H I J
Planned Revised Should I am
Date Date Be Getting
2 03/01/10 True True
3 03/01/10 03/30/10 False False
4 03/01/10 03/18/10 True False
5 03/23/10 True True
6 03/23/10 03/28/10 False True
7 03/23/10 04/11/10 False True


--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---

 

[Rik_UK]

Ron

Using your conditions and example the following formula will work
calculating on working days. You do need to be careful though as in your
example row 4 has an expected date of 03/18/10, with an expected condition of
true, but the date is still within 7 working days of your posting, so i would
expect false. To use the function NETWORKDAYS you need to add in the
'Analysis Toolpak' from the menu - Tools->Add-Ins...->Analysis Toolpak check
box ticked->OK - if the check box was not previously ticked excel will need
to be closed and re-opened to use the addin. This is a one off operation.

=OR(ISBLANK(G2),AND(NETWORKDAYS(G2,TODAY())>7,ISBLANK(H2)),AND(G2<=TODAY(),NETWORKDAYS(H2,TODAY())>7))

If you really wanted calander days then the next equation will do the job
without any add-ins required.

=OR(ISBLANK(G2),AND(G2-TODAY()>7,ISBLANK(H2)),AND(G2<=TODAY(),TODAY()-H2>7))

The above is based on the assumption that you want to be warned when dates
in G2 exceed 7 days from the current date...

Best of luck

 

[Ron Luzius]

Thank You OssieMac, Shane, and Rik_UK.

It is still a no go.

Can I send one of you the XLS so you can see my problem live?

--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---

 

[OssieMac]

Hi Ron,

I think that a little lesson in analyzing your problem might be the best way
to go.

On a blank worksheet enter some dummy data in cells G2 and H2 and then in
cells out to the right enter the functions for each of the smallest
components of your nested function and see if what they return the expected
result.

When I posted my earlier answer I committed the sin of not analyzing the
individual functions; I only looked at your Or and And operators.

One example is WORKDAY(G2,0) which will return whatever value is in G2
irrespective of whether it is a workday or not due to the zero parameter.

If you want to test if G2 is a workday and if it is a workday then return G2
and if not a workday, return the first workday after then you have to enter
the fucntion as follows so that you can use a 1 parameter for the first
workday following a date.

=WORKDAY(G2-1,1)

In the above formula
If G2 = Fri Mar 26 2010 then it returns Fri Mar 26 2010 because it is one
workday day after the previous day of Thu Mar 25 2010.

If G2 =Sat Mar 27 2010 then it returns Mon Mar 29 2010 because Mon is the
first workday after the previous day which is Fri.

If G2 = Sun Mar 28 2010 then it returns Mon Mar 29 2010 because it is the
fiorst workday after Sat

If G2 = Mon Mar 29 2010 then it returns Mon Mar 29 2010 because it is the
first workday after Sun.

Try all of your individual functions on a test worksheet and alter the dates
in G2 and H2 and see if you get the answers you expect for each individual
function. When you get them correct then I am sure you will achieve the rest.
Also if one of the individual functions do not return the expected value and
you can't work out the correct use of the function, then that is the question
you need to ask.

Try it and let me know how it goes. I think if you sort out this Workday
function then you are on the home run.

 

[OssieMac]

Hi Ron,

Based on the examples you posted try the following.

=OR(ISBLANK(G2),AND(G2<=WORKDAY(TODAY(),7),ISBLANK(H2),AND(G2<=TODAY(),H2<=WORKDAY(TODAY(),7))))