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)),"Q 1",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,3 1)),"Q1","Not this Q"))))


Mangesh




"MyKool" wrote in message
...
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

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


"MyKool" wrote:

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

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" wrote in message
...
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

Thanks Bob, very impressive !!

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

Thanks

"Bob Phillips" wrote:

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" wrote in message
...
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