=if(and ??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi can you please help ?

I am trying to insert a formula that works out if a purchase was done in
Qtr1, Qtr2, Qtr3 or Qtr4 of this financial year..

I have a column with dates of purchase in and am trying the following
formula but to no success:
=IF(AND(O3<=1-4-5,O3>=31-7-5),"Qtr1",IF(AND(O3<=1-8-5,O3>=30-9-5),"Qtr2",IF(AND(O3<=1-10-5,O3>=31-12-5),"Qtr3",IF(AND(O3<=1-1-6,O3>=31-3-6),"Qtr4","Not
this fiscal year"))))
Can you please help me...

Many thanks
 
=IF(AND(O3>=DATE(2005,4,1),O3<=DATE(2005,6,30)),"Q1",IF(AND(O3>=DATE(2005,7,
1),O3<=DATE(2005,9,30)),"Q2",IF(AND(O3>=DATE(2005,10,1),O3<=DATE(2005,12,31)
),"Q3",IF(AND(O3>=DATE(2006,1,1),O3<=DATE(2006,3,31)),"Q1","Not this Q"))))


Mangesh
 
1-4-5 etc. are not excel dates! Use DATE() function to create an excel date
value which can be compared to date in cell O3!
Stefi
 
Hi MyKool,

You can't use dates like that, but there is a more direct approach anyway.

Try this

=IF(OR(O3<=-"2005-04-01",O3>=--"2006-03-31"),"Not in this fiscal
year","Qtr"&VLOOKUP(MONTH(O3),{1,4;4,1;7,2;10,3},2))

Note, I think you have the wrong dates in your formula anyway, you have
Apr-01 to Jul-31 for Qtr1, by my calculations it end on Jun-30, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks Bob, very impressive !!

Have not got a clue how the formula works or what any of it means, but it
works !!!

Thanks
 
Back
Top