Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex If/Or/And formula
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: --- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex If/Or/And formula
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: --- . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex If/Or/And formula
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: --- . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex If/Or/And formula
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: --- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex If/Or/And formula
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,ISBL ANK(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()-H27)) 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: --- . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex If/Or/And formula
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: --- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex If/Or/And formula
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. -- Regards, OssieMac "Ron Luzius" wrote: 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: --- . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex If/Or/And formula
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)))) -- Regards, OssieMac "Ron Luzius" wrote: 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: --- . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex IF formula | Excel Worksheet Functions | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
complex formula? | Excel Discussion (Misc queries) | |||
Complex formula | Excel Worksheet Functions | |||
Complex Formula | Excel Worksheet Functions |