Complex If/Or/And formula
in [Programming]
|
Prev: Excel VBA recommendations
Next: Collect value
From: Ron Luzius on 26 Mar 2010 14:57 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: news(a)netfront.net ---
From: OssieMac on 26 Mar 2010 16:17 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())) -- Regards, OssieMac "Ron Luzius" wrote: > 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: news(a)netfront.net --- > . >
From: Shane Devenshire on 26 Mar 2010 17:19 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)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Ron Luzius" wrote: > 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: news(a)netfront.net --- > . >
From: Ron Luzius on 26 Mar 2010 18:30 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: news(a)netfront.net ---
From: Rik_UK on 26 Mar 2010 22:24
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" wrote: > 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: news(a)netfront.net --- > . > |