IF and AND formula for date field

[murkaboris]

Hello:

I have a column that has dates in it in the format of "MM/DD/YY".
I'd like to create a formula that would evaluate this column and return a
value based on the following conditions.

If, lets say "B2" (the date) is less than or equal to DATEVALUE("01/02/09")
and more or equal to DATEVALUE("01/01/10") return the year value of teh "B2"
cell.
Not sure if there is a way but the rest of the dates fall withing 2009 for
which I need to return the year and quarter...ie. 2009 Q1; 2009 Q2 thorugh
Q4. It might not work all within one formula but as far as I get the first
part that would help. I can just manually do the 2009 then.

Thank you.
Monika

 

[jasontferrell]

Try this:
=IF(B2<DATEVALUE("01/02/2009"),YEAR(B2),IF(B2>DATEVALUE
("01/01/2010"),YEAR(B2),"Q"&(INT(MONTH(B2)/4)+1)))

 

[murkaboris]

Hello:

didn't work, it didn't give me any error just showed the formula as typed
in...
Please advise.
Thank you.
Monika

 

[Fred Smith]

Did you put an equal sign in front of the formula? Is the cell formatted for
text? Either of these would cause the problem you described.

It works for me when I test it, so the problem has to be operator error.

Regards,
Fred

 

[murkaboris]

Sure was me, the whole column was formatted as text.
Thank you.

One more adjustment if its possible, for 2009 it returns Q2, Q3, Q4 instead
of 2009 Q2, 2009 Q3...is there a way to adjust the last leg to return the
year and the quarter instead of just the quarter?

Please advise.
Thank you.
Monika

 

[jasontferrell]

Yes, this will do it.
=IF(B2<DATEVALUE("01/02/2009"),YEAR(B2),IF(B2>DATEVALUE
("01/01/2010"),YEAR(B2),"2009Q"&(INT(MONTH(B2)/4)+1)))

 

[murkaboris]

Absolutely wonderful!
Thank you, you have saved me soooo much time .
Monika