=if(and ??

[MyKool]

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

 

[Mangesh Yadav]

=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

 

[Stefi]

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

 

[Bob Phillips]

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)

 

[MyKool]

Thanks Bob, very impressive !!

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

Thanks