Complex If/Or/And formula
in [Programming]
|
Prev: Excel VBA recommendations
Next: Collect value
From: Ron Luzius on 27 Mar 2010 02:46 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: news(a)netfront.net ---
From: OssieMac on 27 Mar 2010 06:50 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: news(a)netfront.net --- > . >
From: OssieMac on 27 Mar 2010 17:43
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: news(a)netfront.net --- > . > |