Help needed for excel formulas!!!

[nikko]

Hi, need help on below:-

i'm trying to have a formula that show if the Close date has been changed
for data as of May 2008 and June 2008 and if this change is a Push-out,
Pull-In or No change.
I will also like the formula to show if the close date as of June 2008 is
within the quarter or outside of the quarter.

Below is the formula i've tried to use but return no value.
=IF(AND(F2>F3, F3>39538, F3<39630), "In-In", "In-Out"), IF(AND(F2<F3,
F3>39538, F3<39630), "Out-In", "Out-Out"), IF(AND(F2=F3, F3>39538, F3<39630),
"NC-In", "NC-Out")

F2 - close date as of May 2008
F3 - close date as of June 2008
If close date is between 1 April to 30 Jun 2008, then it's within the quarter
If close date is not within this period, then it should show as Outside of
quarter

Please help . thanks

 

[Don Guillett]

I'm confused.
Post your actual layout or send a workbook to my personal address below with
exactly what you want.

 

[nikko]

Hi Don
Here's a sample of the data..

For every opp, there are 2 rows of data. 1st row is the data as of May and
2nd, as of June.

I need a formula to tell if the Close date has been updated/revised between
these 2 period. if the date has been updated, is this a push out or pull in
and if the new date as of June is within the qtr or outside of qtr.

Country Opportunity Owner Opportunity ID Opportunity Name As of
Date Historical Close Date Historical Amount (converted)
China Cindy Opp1 ABC-1111 5/1/2008 5/31/2008 204,926
China Cindy Opp1 ABC-1111 6/1/2008 9/30/2008 90,463
China Andy Opp2 ABC-2222 5/1/2008 8/19/2008 111,694
China Andy Opp2 ABC-2222 6/1/2008 8/19/2008 111,694
China Ben Opp3 ABC-3333 5/1/2008 6/20/2008 912,011
China Ben Opp3 ABC-3333 6/1/2008 9/30/2008 912,011
China Cindy Opp4 ABC-4444 5/1/2008 5/23/2008 33,785
China Cindy Opp4 ABC-4444 6/1/2008 6/27/2008 33,785
Australia Jason Opp5 ABC-5555 5/1/2008 6/20/2008 162,231
Australia Jason Opp5 ABC-5555 6/1/2008 6/20/2008 162,231

Thanks!
nikko

 

[Don Guillett]

To start with
The first part of your formula asks if f2>f3. How can the as of date be
later than the close?
Then you answer the if in the first part without giving the second and third
a chance.

=if(and(cond1,cond2),ans and stop, second if